Wednesday, March 9, 2011

SQL Server: How to Avoid Big Single Error Log File on Production Servers

One thing, strange I found on our production database servers was a BIG SINGLE Error log file. It was taking long time to open and exploring it for required information was even worse. This happened because production database servers never go down and SQL Server is keeping error log to a single file. On every restart SQL Server initiates a new error log file but for production servers, restart occurs after very long time. That is why error log file was growing to a very large size.
Only solution for this problem is system stored procedure sp_cycle_errorlog. This system stored procedure is used to cycle error log file without restarting SQL Server. Executing this stored procedure with job or manually helps to cycle the error log file periodically.

Exec master.dbo.sp_cycle_errorlog


All suggestions are welcome