By default, SQL Server saves 7 ErrorLog files, named:
ErrorLog
Errorlog.1
Errorlog.2
Errorlog.3
Errorlog.4
Errorlog.5
Errorlog.6
In APACS OS version 6.1, the ErrorLog file is saved in the C:\Program Files\Microsoft SQL Server\mssql$wincc\log folder. In APACS OS version 7.0, the ErrorLog file is saved in the C:\Program Files\Microsoft SQL Server\mssql.1\mssql\log folder. The ErrorLog file contains the most up-to-date information; the Errorlog.6 file contains the oldest information.
Each time you restart SQL Server, the log files are cycled as follows:
Delete all the data in the Errorlog.6 file and create a new ErrorLog file.
All data in the last ErrorLog file is written to the Errorlog.1 file.
All data in the last Errorlog.1 file is written to the errorlog.2 file.
All data in the last Errorlog.2 file is written to the errorlog.3 file.
All data in the last Errorlog.3 file is written to the errorlog.4 file.
All data in the last Errorlog.4 file is written to the errorlog.5 file.
All data in the last Errorlog.5 file is written to the errorlog.6 file.
If one of the ErrorLog files is already large, you can manually loop the ErrorLog files by running the sp_cycle_errorlog stored procedure. Note: The data in the old ErrorLog file will be overwritten! If you must save the data in the old ErrorLog file, you can copy the old ErrorLog files to an external storage medium.
In APACS OS version 6.1, you can run sp_cycle_errorlog stored procedures from SQL Server Enterprise Manager using SQL Qry Analyzer Tool. In APACS OS version 7.0, sp_cycle_errorlog stored procedures can be run from SQL Server Management St io.
So you need to be able to switch write error log periodically. You can generally use the DBCC ERRORLOG command to operate.
Exec (' DBCC ErrorLog ')
Alternatively, the SP can be placed in the job on a regular basis by using the following command.
Copy Code code as follows:
CREATE PROCEDURE Sp_cycle_errorlog---1997/06/24
As
if (Is_srvrolemember (' sysadmin ') = 1)--Make sure ' it's the SA executing this.
Begin
RAISERROR (15247,-1,-1)
Return (1)
End
DBCC ERRORLOG
Return (0)
Go
Copy Code code as follows:
Get all tables in the database
SELECT Sysobjects.name as tablename from sysobjects WHERE xtype = ' U ' and sysstat<200
Gets the column names of all tables in the database
SELECT Syscolumns.name as Columnsname, sysobjects.name as tablename from sysobjects, syscolumns WHERE sysobjects.xtype= ' u ' And Sysobjects.id=syscolumns.id
Get SQL All database names, all table names, all field names, table field lengths
1. Get all database names in MSSQL:
SELECT name from MASter. sysdatabases ORDER BY name
2. Get all user table names in MSSQL:
SELECT name from DatabASename.. sysobjects WHERE xtype= ' U ' ORDER by name
Xtype= ' U ': represents all user tables;
Xtype= ' S ': represents all system tables;
3. Get all field names for the specified table [Tb_phone]:
SELECT name from syscolumns WHERE id=object_id (' Tb_phone ')
4.SQL table name, all field names, table field length for all tables
SELECT table_name as data table name,
column_name as field name,
ISNULL (Column_default, ') as default value,
Is_nullable as is allowed NULL,
Data_type as data type,
ISNULL (ISNULL (ISNULL (character_maximum_length,numeric_precision), datetime_precision), 1) as type length
From Information_schema.columns
WHERE not table_name in (' Sysdiagrams ', ' dtproperties ')
5. Gets the table name of the specified table [Tb_phone], the name of the field, the type of fields, and the length of the type
SELECT Sysobjects.name as TableName,
Syscolumns.name as Columnsname,
Systypes.name as Datetype,
Syscolumns.length as Datelength
From sysproperties right OUTER JOIN
sysobjects INNER JOIN
syscolumns on sysobjects.id = syscolumns.id INNER JOIN
systypes on syscolumns.xtype = Systypes.xtype on
Sysproperties.id = Syscolumns.id and
Sysproperties.smallid = Syscolumns.colid
WHERE (Sysobjects.xtype = ' u ' OR
Sysobjects.xtype = ' V ') and (systypes.name <> ' Sysname ') and
(sysobjects.name = ' tb_phone ')
ORDER BY Columnsname
Every database in SQL Server, whether it is a system database (Master,model, Sybsystemprocs, tempdb), or a user database, has its own transaction log, and each library has a syslogs table. Log records the user's modifications to the database, so if the command is not cleared, the log will grow up to fill up the space. Clear Log Available DUMP TRANSACTION command, or Open database option trunc Log on chkpt, the database automatically clears log at intervals. Managing database log is one aspect that users must consider when manipulating a database.
One, delete log
1: Detach Database Enterprise Manager-> Server-> Database-> Right key-> Detach database
2: Delete log file
3: Additional database Enterprise Manager-> Server-> Database-> Right key-> attached database
This method generates a new log with a size of more than 520 K
And then set this database to automatically shrink
or in code:
The following example separates 77169database and then attaches a file in 77169database to the current server.
Copy Code code as follows:
EXEC sp_detach_db @dbname = ' 77169database '
EXEC sp_attach_single_file_db @dbname = ' 77169database ',
@physname = ' c:\Program Files\Microsoft SQL Server\mssql\data\77169database.mdf '
Second, empty the log
Copy Code code as follows:
DUMP TRANSACTION Library name with NO_LOG
Re-operation:
Enterprise Manager--right key to the database you want to compress--all tasks--shrink the database--Shrink the file--select log file--in the contraction way to choose to shrink to XXM, here will give a allow to shrink to the minimum m number, directly enter this number, OK
Third, if you want to not let it grow
Enterprise Manager-> Server-> Database-> Properties-> Transaction Log-> Limit file growth to 2M
Automatically shrink the log, or you can use the following statement:
ALTER database name SET auto_shrink on
The failover model is changed to simple, with the statement:
Copy Code code as follows:
Use MASTER
Go
ALTER database name SET RECOVERY simple
Go
------------------------------------------
Truncate transaction log:
BACKUP LOG {database_name | @database_name_var}
{
[With
{no_log | TRUNCATE_ONLY}]
}
--Compressed log and database file size
/*--Special attention
Follow the steps, do not follow the steps, or you may damage your database.
--*/
1. Empty log
DUMP TRANSACTION Library name with NO_LOG
2. Truncate the transaction log:
BACKUP LOG database name with NO_LOG
3. Shrink the database file (if not compressed, the database file will not be reduced
Enterprise Manager--right--the database you want to compress--all tasks--shrink the database--Shrink the file
--Select Log file--in the shrinkage method to choose to shrink to XXM, here will give a allow to shrink to the minimum m number, directly enter this number, you can determine
--Select the data file--in the contraction way to choose to shrink to XXM, here will give a allow to shrink to the minimum m number, directly enter this number, OK
You can also use SQL statements to complete
--Shrinking the database
DBCC shrinkdatabase (Customer information)
--Shrink the specified data file, 1 is the file number, you can query through this statement:
SELECT * FROM Sysfiles DBCC shrinkfile (1)
4. In order to maximize the reduction of log files (if it is SQL 7.0, this step can only be done in Query Analyzer)
A. Detaching a database:
Enterprise Manager--server--database--right key--detach database
B. Delete log files on my Computer
C. Additional databases:
Enterprise Manager--server--database--right Key--Append database This method will generate a new log with a size of more than 500 k
or in code:
The following example separates 77169database and then attaches a file in 77169database to the current server.
A. Separation
EXEC sp_detach_db @dbname = ' 77169database '
B. Deleting a log file
C. Additional
EXEC sp_attach_single_file_db @dbname = ' 77169database ', @physname = ' c:\Program Files\Microsoft SQL Server\mssql\data\ 77169database.mdf '
5. In order to be able to automatically shrink later, do the following settings:
Enterprise Manager--server--right key database--Properties--Options--select "Auto Shrink"
--sql Statement setting:
EXEC sp_dboption ' database name ', ' autoshrink ', ' TR '
6. If you want to not let it grow too big log
Enterprise Manager--server--right key database--attribute--transaction log
--Limit file growth to XM (x is the maximum data file size you allow)
How the--sql statement is set:
Copy Code code as follows:
ALTER DATABASE name modify file (name= logical filename, maxsize=20)
--------------------------------------------------------------
/*--Common stored procedures for compressed databases
Compress the log and database file size because the stored procedure cannot be created in the compressed database to be detached from the database.
/*--Call Example
EXEC p_compdb ' test '
--*/
Use master-Note that this stored procedure is built in the master database
Go
if exists (SELECT * from dbo.sysobjects where ID
= object_id (N ' [dbo].[ P_COMPDB] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_compdb]
Go
Create proc P_compdb
@dbname sysname--The name of the database to compress
@bkdatabase bit=1, because the database may be corrupted in the steps of separating the logs, so you can choose whether to automatically database
@bkfname nvarchar (260) = '--the file name that is backed up, if not specified, automatically backed up to the default backup directory,
Backup file Name: Database name + Date Time
As
--1. Empty log
EXEC (' DUMP TRANSACTION [' + @dbname + '] with no_log ')
--2. Truncate the transaction log:
EXEC (' BACKUP LOG [' + @dbname + '] with no_log ')
--3. Shrink the database file (if not compressed, the database file will not be reduced
EXEC (' DBCC shrinkdatabase ([' + @dbname + ']) ')
--4. Set Auto Shrink
EXEC (' exec sp_dboption ' + @dbname + ', ' autoshrink ', ' ' TR ')
--there is a certain danger behind the steps, and you can choose whether these steps should
--5. Detach the database
Copy Code code as follows:
If @bkdatabase =1
Begin
If IsNull (@bkfname, ') = '
Set @bkfname = @dbname + ' _ ' +convert (Varchar,getdate (), 112)
+replace (CONVERT (Varchar,getdate (), 108), ': ', '
Select message = ' Back up database to SQL default backup directory, Backup filename: ' + @bkfname
EXEC (' backup database [' + @dbname + '] to disk= ' + @bkfname + ' ")
End
--Separate processing
CREATE table #t (fname nvarchar), type int)
EXEC (' INSERT into #t select filename,type=status&0x40 from [' + @dbname + '] ... Sysfiles ')
EXEC (' sp_detach_db ' + @dbname + ' ")
--Delete log files
declare @fname nvarchar (@s), varchar (8000)
Declare TB cursor Local to select fname from #t where type=64
Pen TB
FETCH NEXT from TB into @fname
While @ @fetch_status =0
Begin
Set @s= ' del ' +rtrim (@fname) + ' "'
EXEC master.. xp_cmdshell @s,no_output
FETCH NEXT from TB into @fname
End
Close TB
Deallocate TB
--Additional databases
Set @s= '
Declare TB cursor Local to select fname from #t where type=0
Open TB
FETCH NEXT from TB into @fname
While @ @fetch_status =0
Begin
Set @s=@s+ ', ' +rtrim (@fname) + ' "
FETCH NEXT from TB into @fname
End
Close TB
Deallocate TB
EXEC (' sp_attach_single_file_db ' + @dbname + ' "+@s)
Go
