Create Error Log Loop

Whenever SQL Server is restarted, it creates a new error log. if theserver stays up for a while, this can make for a very large error log. I create a job that runs on a daily or weekly basis that executesSp_cycle_errorlogStored Procedure in the master database. This will create a new error log without having to restart SQL Server.

Sp_cycle_errorlog

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.


Note
When SQL Server is started, the current error log is renamed to errorlog.1, errorlog.1 to errorlog.2, errorlog.2 to errorlog.3, and so on. Sp_cycle_errorlog allows you to cyclically access error log files without stopping and starting the server.

Permission
The execution permission of sp_cycle_errorlog is limited to members of the SysAdmin fixed server role.

Example
The following example loops through SQL server error logs.

Copy code 
Exec sp_cycle_errorlog;
Go