Use sp_cycle_errorlog to loop SQL server error logs

Because high-availability database servers may rarely shut down, SQL Server's log files will grow very large. In this way, it is difficult for DBA to use error log to find information, and the performance will be affected when the log is large.

 

We usually think that error logs will be created when sqlserver is started, so you need to stop the log files to re-create the log files. Such maintenance is unimaginable for high-availability databases.

 

In fact, you can achieve a log loop without restarting the service. Microsoft provides a stored procedure sp_cycle_errorlog to implement a log loop. This stored procedure is used to close the current error log file and cycle the error log extension number (like restarting the server ). The new error log contains the version and copyright information, and a line indicating that the new log has been created.

 

The current error log is renamedErrorlog.1;Errorlog.1ChangeErrorlog.2,Errorlog.2ChangeErrorlog.3And so on.Sp_cycle_errorlog
Allows you to cyclically access error log files without having to stop and start the server.

 

The following example loops through SQL server error logs.

 

Exec sp_cycle_errorlog;

 

Therefore, you only need to create a job to regularly execute the stored procedure as needed to manage error logs. (Convenient ).