For more information about SQLServerErrorLog, see.
For more information about SQL Server ErrorLog, see.
By default, SQL Server saves seven ErrorLog files named:
ErrorLog
ErrorLog.1
ErrorLog.2
ErrorLog.3
ErrorLog.4
ErrorLog.5
ErrorLog.6
In apacs OS 6.1, The ErrorLog file is saved in the c: \ Program Files \ Microsoft SQL Server \ MSSQL $ WINCC \ LOG folder. In apacs OS 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 latest information; The ErrorLog.6 file contains the oldest information.
Each time you restart SQL Server, these log files are in the following cycle:
Delete all data in ErrorLog.6 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 large, you can manually cycle these 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 these old ErrorLog files to an external storage medium.
In apacs OS 6.1, you can use SQL Qry Analyzer Tool to run the sp_cycle_errorlog stored procedure from SQL Server Enterprise Manager. In apacs OS 7.0, you can run the sp_cycle_errorlog stored procedure from SQL Server Management St io.
Therefore, you need to switch to the error log periodically. Generally, you can use the DBCC errorlog command to perform operations.
Exec ('dbcc errorlog ')
Alternatively, run the following command to periodically execute the sp in the Job.
The Code is as follows:
Create procedure sp_cycle_errorlog --- 1997/06/24
As
If (not (is_srvrolemember ('sysadmin') = 1) -- Make sure that it is the SA executing this.
Begin
Raiserror (15247,-1,-1)
Return (1)
End
Dbcc errorlog
Return (0)
GO
The Code is as follows:
Obtain all tables in the database
SELECT SysObjects. name AS Tablename FROM sysobjects WHERE xtype = 'U' and sysstat <200
Obtains the 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
Obtain all SQL database names, all table names, all field names, and table field lengths.
1. Retrieve 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': indicates all user tables;
XType = 's': indicates all system tables;
3. Obtain all field names of the specified table [tb_phone:
SELECT name FROM SysColumns WHERE id = Object_Id ('tb _ phone ')
4. Names of all SQL tables, all field names, and table field lengths
SELECT table_name AS data table name,
Column_name AS field name,
ISNULL (column_default, '') AS default value,
Whether is_nullable AS can be NULL,
Data_type AS data type,
ISNULL (character_maximum_length, numeric_precision), datetime_precision), 1) AS type Length
FROM information_schema.columns
Where not table_name IN ('sysdiagrams ', 'dtproperties ')
5. Obtain the table name, field name, field type, and type length of the specified table [tb_phone ].
SELECT SysObjects. name AS Tablename,
Syscolumns. name AS Columnsname,
Policypes. name AS DateType,
Syscolumns. length AS DateLength
FROM Sysproperties RIGHT OUTER JOIN
Sysobjects INNER JOIN
Syscolumns ON Sysobjects. id = Syscolumns. id INNER JOIN
Policypes ON Syscolumns. xtype = policypes. xtype ON
Sysproperties. id = Syscolumns. id AND
Sysproperties. smallid = Syscolumns. colid
WHERE (Sysobjects. xtype = 'U' OR
Sysobjects. xtype = 'V') AND (policypes. name <> 'sysname') AND
(Sysobjects. name = 'tb _ phone ')
Order by Columnsname
Each database of SQL Server, whether it is a system database (master, model, sybsystemprocs, tempdb) or a user database, has its own transaction log, and each database has a syslogs table. Logs record user operations on the database, so if you do not need to clear the Log, the log will continue to grow until the occupied space. You can run the dump transaction command to clear logs, or use the trunc log on chkpt option to clear logs automatically at intervals. Managing database logs is a must for users to operate databases.
1. delete a LOG
1: Detach Database Enterprise Manager-> server-> database-> right-click-> detach Database
2: delete LOG files
3: attach the Database Enterprise Manager-> server-> database-> right-click-> attach Database
This method generates a new LOG with a size of more than 520 KB.
Then set the database to automatically contract
Or use the code:
The following example separates the 77169 database and then attaches a file in the 77169 database to the current server.
The Code is 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'
Ii. Clear logs
The Code is as follows:
Dump transaction database name WITH NO_LOG
Next:
Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file -- Select log file -- select to contract to XXM in the contract mode, here we will provide a minimum number of M that can be reduced. Enter this number directly and click OK.
3. If you want to avoid growth in the future
Enterprise Manager-> server-> database-> properties-> transaction log-> limit file growth to 2 MB
You can also use the following statement to automatically contract logs:
Alter database name SET AUTO_SHRINK ON
The fault recovery model is changed to simple. The statement is as follows:
The Code is as follows:
USE MASTER
GO
Alter database name SET RECOVERY SIMPLE
GO
------------------------------------------
Transaction Log truncation:
Backup log {database_name | @ database_name_var}
{
[
{NO_LOG | TRUNCATE_ONLY}]
}
-- Compressed log and database file size
/* -- Pay special attention
Follow these steps. Do not follow these steps. Otherwise, your database may be damaged.
--*/
1. Clear logs
Dump transaction database name WITH NO_LOG
2. truncate transaction logs:
Backup log database name WITH NO_LOG
3. Compress database files (if not compressed, the database files will not be reduced
Enterprise Manager -- Right-click the database you want to compress -- all tasks -- contract database -- contract file
-- Select log file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.
-- Select data file -- select to shrink to XXM in the contraction mode. Here, a minimum number of MB allowed to be shrunk is displayed. Enter this number directly and click OK.
You can also use SQL statements to complete
-- Shrink Database
Dbcc shrinkdatabase (customer profile)
-- Contract the specified data file. 1 indicates the file number. You can use this statement to query:
Select * from sysfiles dbcc shrinkfile (1)
4. To minimize log files (for SQL 7.0, this step can only be performed in the query analyzer)
A. Separate the database:
Enterprise Manager -- server -- database -- Right-click -- detach Database
B. Delete LOG files in my computer
C. Additional database:
Enterprise Manager -- server -- database -- Right-click -- attach database this method will generate a new LOG with a size of more than 500 KB
Or use the code:
The following example separates the 77169 database and then attaches a file in the 77169 database to the current server.
A. Separation
EXEC sp_detach_db @ dbname = '77169database'
B. Delete log files
C. Attach
EXEC sp_attach_single_file_db @ dbname = '77169database', @ physname = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL \ Data \ 77169database. mdf'
5. In order to automatically contract in the future, make the following settings:
Enterprise Manager -- server -- Right-click Database -- Property -- option -- select "auto contract"
-- SQL statement setting method:
EXEC sp_dboption 'database name', 'autoshrink', 'tr'
6. If you want to prevent the log from increasing too much in the future
Enterprise Manager -- server -- Right-click Database -- properties -- transaction log
-- Limit file growth to xM (x is the maximum data file size you allow)
-- SQL statement settings:
The Code is as follows:
Alter database name modify file (name = logical file name, maxsize = 20)
--------------------------------------------------------------
/* -- Compress the General stored procedure of the database
The size of the compressed logs and database files. Because the database must be separated, the stored procedure cannot be created in the compressed database.
/* -- Call example
Exec p_compdb 'test'
--*/
Use master -- note that this stored procedure should be created 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, -- Name of the database to be compressed
@ Bkdatabase bit = 1, -- because the log separation step may damage the database, you can choose whether to automatically Database
@ Bkfname nvarchar (260) = ''-- Name of the backup file. If this parameter is not specified, it is automatically backed up to the default backup directory,
Backup File Name: database name + Date and Time
As
-- 1. Clear logs
Exec ('dump TRANSACTION ['+ @ dbname +'] WITH NO_LOG ')
-- 2. truncate transaction logs:
Exec ('backup LOG ['+ @ dbname +'] WITH NO_LOG ')
-- 3. compress the database file (if it is not compressed, the database file will not be reduced
Exec ('dbcc SHRINKDATABASE (['+ @ dbname +']) ')
-- 4. Set automatic contraction
Exec ('exec sp_dboption ''' + @ dbname + ''', ''autoshrink '', ''tr ''')
-- The subsequent steps are dangerous. You can choose whether to perform these steps.
-- 5. Database Separation
The Code is as follows:
If @ bkdatabase = 1
Begin
If isnull (@ bkfname, '') =''
Set @ bkfname = @ dbname + '_' + convert (varchar, getdate (), 112)
+ Replace (convert (varchar, getdate (), 108 ),':','')
Select prompt = 'Back Up the database to the default SQL Backup Directory. Backup File Name: '+ @ bkfname
Exec ('backup database ['+ @ dbname +'] to disk = ''' + @ bkfname + '''')
End
-- Separate
Create table # t (fname nvarchiar (260), type int)
Exec ('insert into # t select filename, type = status & 0x40 from ['+ @ dbname +'] .. sysfiles ')
Exec ('SP _ detach_db ''' + @ dbname + '''')
-- Delete a log file
Declare @ fname nvarchar (260), @ s varchar (8000)
Declare tb cursor local for 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_mongoshell @ s, no_output
Fetch next from tb into @ fname
End
Close tb
Deallocate tb
-- Attach a database
Set @ s =''
Declare tb cursor local for 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
