How To Recycle SQL Server Error Log

Command:  sp_cycle_errorlog :

Few Facts:

• SQL Server can maintain from 6 - 99 Error Logs ; default is 6. The currently used SQL Server Error Log will be named errorlog.1

• Every time SQL Server is started, the current error log is renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes errorlog.3, and so on.

• sp_cycle_errorlog enables you to cycle the error log files without stopping and starting the server.

Syntax :

EXEC msdb.dbo.sp_cycle_errorlog

GO

User case: Why sql server error log should be recycle

1.              If nature of business doesn't allow you to restart SQL server at-least once a month
2.              If you are capturing lot of activities in SQL server
3.        If you are debugging some issue and don't want to read older content
4.        If there is some error and SQL server is generating lot of logs in error log

Blog is own by Algae Servies (algaeservices.co.in)