>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
Advertisements

Posted on March 9, 2011, in Sql Server 2005, Sql Server 2008, Sql Server Management, Store Procedures. Bookmark the permalink. 2 Comments.

  1. >Thanks for your post Aasim, always useful. Regards, Arif.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: