Thursday, August 29, 2013

Cycle Your Error Logs

One thing I like to configure in all of my database instances is the rate at which my error logs are recycled.  If you don't recycle them on a regular basis, they can tend to grow to be very large, often making it difficult to open them.  By default they don't recycle until a restart of the service, respectively.

The first thing I do to address this is to roll them over each night by creating a SQL Server job that runs at midnight.  That keeps them consistent and relatively small.

EXEC sp_cycle_errorlog
EXEC sp_cycle_agent_errorlog  

Next what I will do is increase the Maximum number of error logs files to the number of days I wish to retain.  Most of the time one month is sufficient.  To do this connect to your instance and expand Management.  Right-click on SQL Server Logs, check the box and change the value to 30 or whatever your desired days of retention are.

In the event you are interested in knowing where these logs are stored, they are typically located at C:\Program Files\Microsoft SQL Server\MSSQL.<instance>\MSSQL\LOG.

I hope you find this useful!