Microsoft – SQLServer – ErrorLog File – Grooming

Microsoft – SQLServer – ErrorLog File – Grooming

MS SQL Server ErrorLog files can grow for various reasons:

  1. Resource Issues – Particularly Low Storage
  2. TraceFlag Configuration
  3. Frequent backup, particularly frequent transaction backups which are logged as informational enytries

There are many other reasons.  But, what to do when your error log file has grown to be over a few hundred MB or GBs

Well, see if any of the following applies to you:

  1. Is MS SQL Installed on your System Drive (C:), if so that will create other problems.  It is not easy to change that situation.  If a re-install is a day too late, then using MS SQL Server Configuration Manager change the filename of your errorlog ( -e option).Please note that this entry is the fully qualified file name of your error log file…It is not the folder’s name.  It is the file name and SQL Server takes care of “journaling” the numbers at the end of the filename as it generates new files
  2. Consider using sp_cycle_errorlog to in-activate the current errorlog and thus create a new one that will then be the active one.Once we have a new active log, the system will allow you to move to simple the former active one.

References:

1) Using the SQL Server Service Startup Options
http://msdn.microsoft.com/en-us/library/ms190737(v=sql.90).aspx

2) How to: Configure Server Startup Options (SQL Server Configuration Manager)
http://msdn.microsoft.com/en-us/library/ms345416(v=sql.90).aspx

3) sp_cycle_errorlog (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms182512.aspx

4) Where is SQL Server ErrorLog?
http://beyondrelational.com/modules/2/blogs/44/posts/13728/help-where-is-sql-server-errorlog.aspx 

 

Leave a Reply

Please log in using one of these methods to post your comment:

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