SQL Server Error Log

During routine maintenance of SQL Server, you can view the SQL Server Error Log to confirm the running status of the service, such as service start/stop, backup and restore operations, and logon authentication, you need to check it regularly.

You can use SQL Server Management studio or a text editor to view SQL server error logs. By default, error logs are stored in the program files/Microsoft SQL Server/MSSQL. N/MSSQL/log/errorlog and errorlog. N files. Seven SQL Server Error Log files, including errorlog and errorlog.1 ~, are retained by default ~ Errorlog.6. The current error log (File errorlog) has no extension. Each time an SQL server instance is started, a new Error Log errorlog is created, and the previous errorlog is changed to errorlog.1, the previous errorlog.1 is changed to errorlog.2, and so on. The original errolog.6 is deleted.

The errorlog file on the production server sometimes encounters a large file, especially when the logon authentication is recorded in the error log, in this case, using SQL Server Management studio or a text editor to view error logs may cause a problem. In this case, you can restart the server without restarting the server, use sp_cycle_errorlog to generate a new log file and cycle the extension number of the Error Log, just as when the service is restarted. In addition to execute sp_cycle_errorlog, you can also use DBCC errorlog to implement the same function. In practice, you can also create a job to regularly execute the stored procedure, so that the log file size is controlled within a reasonable range.

By default, SQL Server retains seven Error Log Files. When a new error log is generated, the oldest log is deleted. If you want to retain more error logs, you can set it as follows (SQL Server 2005 ):

Open SQL Server Management Studio
In the "manage" directory, right-click the SQL server log and click "Configure"
In the "Configure SQL Server Error Log" window that appears, check the "Limit the number of error log files before collection" check box, set "Maximum number of Error Log Files" to the expected value. The value ranges from 6 to 99.
In addition to the preceding methods, you can modify the registry. Create a new registry key (if any): HKEY_LOCAL_MACHINE/software/Microsoft/MSSQLServer/numerrorlogs. By default, this registry key is not available. select REG_DWORD for the type, the number of logs to be retained. You can use the following stored procedure to modify the registry key:

Exec xp_instance_regwrite n 'HKEY _ LOCAL_MACHINE ', n' software/Microsoft/MSSQLServer', n' numerrorlogs', REG_DWORD, 20

By default, sqlserver error logs are stored in the program files/Microsoft SQL Server/MSSQL. N/MSSQL/log/errorlog and errorlog. N files.

You can modify the path as follows:

In the SQL Server Configuration Manager, click "SQL Server service ".
In the right pane, right-click SQL Server (<Instance name>) and then click Properties ".
On the "advanced" tab, the "startup parameters" box contains parameters separated by semicolons. Here, there is a parameter starting with-E. You can modify the path after this parameter to store the error log to the specified path. After the modification is complete, restart the service to take effect.

 

Run sp_cycle_errorlog to manually cycle errorlog

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.