SQL Server error logs can fill up quickly, and when you are troubleshooting something you may need to go through huge log. However you can cycle the error log to manage the amount of log you need to go through. When you cycle error log the current log file is renamed from ERRORLOG to ERRORLOG.1, ERRORLOG.1 is renamed to ERRORLOG.2 and so on.

Restarting SQL Server will cycle the error logs. You can also use sp_cycle_errorlog to cycle error logs without restarting SQL Server.

Below is an example how to use it:

EXEC sp_Cycle_ErrorLog

GO

Result Set:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You can verify that the log has cycled using Management Studio or you can use xp_ReadErrorLog also.

Same effect can be achieved by using DBCC ErrorLog. In fact, sp_Cycle_ErrorLog calls DBCC ErrorLog to cycle error logs.

DBCC ErrorLog

GO

Result Set:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC ErrorLog and sp_Cycle_ErrorLog only cycles SQL Server error logs. It does not cycle SQL Server Agent error logs. In order to achieve this you can restart SQL Server Agent or use another stored procedure sp_Cycle_Agent_ErrorLog.

USE   [msdb]

GO

EXEC  sp_Cycle_Agent_ErrorLog

GO

Result Set:

Command(s) completed successfully.

You can verify that the SQL Server Agent log has cycled using xp_ReadErrorLog.

* Use sp_helptext to see what is called from sp_Cycle_Agent_ErrorLog.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Management Studio, SQL Agent, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012