SQL Server error log shrink (ERRORLOG)

First, basic knowledge

By default, the error log is located at:

C:\Program Files\Microsoft SQL Server\mssql.1\mssql\log\errorlog

and ERRORLOG.N files. The default retention is 7 SQL Server error log files: errorlog,errorlog.1~errorlog.6, the current error log (file ErrorLog) does not have an extension. Whenever you start an instance of SQL Server, a new error log errorlog is created and the previous errorlog is renamed to Errorlog.1, before the Errorlog.1 is renamed Errorlog.2, and so on, the original errolog.6 is deleted.

Ii. contraction of errorlog documents

errorlog files on a production server can sometimes run into large files, especially when logging authentication is logged to the error log, which can be a problem when you use SQL Server Management Studio or a text editor to view the error log. In this case, you can generate a new log file through stored procedure Sp_cycle_errorlog without restarting the server, and loop the error log extension number as if you were restarting the service. In addition to Execute Sp_cycle_errorlog, you can use DBCC ERRORLOG to achieve the same functionality. In practice, you can also execute the stored procedure by establishing a job, which controls the size of the log file within a reasonable range.

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.

EXEC (' DBCC ErrorLog ') or exec sp_cycle_errorlog, or you can place the SP in the job on a regular basis by using the following command.

Third, errorlog other configuration

(i) SQL Server retains 7 error log files by default, and the oldest log is deleted while the new error log is generated, and if you want to keep more of the error log, you can set it in the following ways (SQL Server 2005):

Open SQL Server Management Studio

In the administration directory, right-click the SQL Server log and click Configure

In the Configure SQL Server error log window that pops up, tick the limit number of error log files before recycling check box, and set the maximum error log file count to the desired value. This value is between 6 and 99.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

In addition to the above methods, you can modify the registry by modifying the form.

Create a new registry key (if it is modified):

Hkey_local_machine/software/microsoft/mssqlserver/mssqlserver/numerrorlogs, by default, does not have this registry entry, type selection REG_DWORD, The numeric value is set to the number of logs that you want to keep. The method of modifying a registry key can also be implemented through the following stored procedures:

EXEC xp_instance_regwrite n ' HKEY_LOCAL_MACHINE ', n ' software/microsoft/mssqlserver/mssqlserver ', n ' NumErrorLogs ', REG_DWORD, 20

(ii) By default, SQL Server error logs are located in: Program Files/microsoft SQL Server/mssql.n/mssql/log/errorlog and ERRORLOG.N files. You can modify the path by using the following methods:

In SQL Server Configuration Manager, click SQL Server Services.

In the right pane, right-click SQL Server (< instance name >), and then click Properties.

On the Advanced tab, in the Startup parameters box, you have a semicolon (;) The delimited parameter. In which, there is a parameter beginning with –e, modify the path after the parameter, you can deposit the error log under the specified path, the modification needs to restart the service to take effect.

(c) If you want to filter the error log file query, you can refer to: SQL Server error log filtering (ERRORLOG)

Author: Listen to the wind blowing rain

Source: http://gaizai.cnblogs.com/