Best Practices – Recycle SQL Server Error Logs

It’s been a while since my previous post, was working on my own database server. More info on that soon!

Today I’ve received a great tip from my colleague Berrie Roelofs (experienced DBA) to monitor and recycle Error Logs often (like once a week or so) on database servers which are continuously running in a Production Environment.

In most production environments, the SQL Server is restarted very rarely as a result both SQL Server Error Log and SQL Server Agent Log keeps growing and at times it becomes very difficult to open up and analyze Error Logs when you encounter issues. However, it will be a good practice for a DBA to schedule a SQL Server Agent Job which runs once in a week to execute system stored procedures to create a new SQL Server Agent Error Log and clear the SQL Server Error Logs.

SQL Server Agent Error Log (SQLAGENT.OUT)

The SQL Server Agent Error Log is a great place to find information about what is happening in your SQL Server Agent. Each SQL Server Agent Error log will record informational, warnings and different error messages which have occurred since SQL Server Agent was last restarted or since the last time you have recycled the SQL Server Agent Error Log.

By default, the SQL Server Agent Error Log is located in “Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\SQLAGENT.OUT”. A new error log is created when an instance of SQL Server Agent is restarted. Database Administrators can execute sp_cycle_agent_errorlog system stored procedure to recycle the SQL Server Agent Error Logs without recycling the instance of SQL Server Agent.

How to recycle the SQL Server Agent Error Log

Option 1

Use the GUI of SSMS (or SSDT in SQL Server 2012) to recycle it manually:

(Right-Click on ‘Error Logs’)

Option 2

Schedule a SQL Agent Job that recycles the SQL Server Agent Log:
(Execute permissions for sp_cycle_agent_errorlog are restricted to members of the sysadmin fixed server role.)

MSDN: sp_cycle_agent_errorlog

EXEC dbo.sp_cycle_agent_errorlog ;
GO

SQL Server Error Logs

By default, SQL Server will keep six archived error log files along with the error log that is currently used. However, you can override this number if you wish to archive fewer or more logs. This can be accomplished by right clicking on SQL Server Logs within Enterprise Manager and choosing “Configure”.

The SQL Server Error Logs are located in “Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG.n”.

The most current error log file is called ERRORLOG. If you stop and re-start SQL Server, the old log will be archived and a new one will be created.
Or use T-SQL to locate the Error Logs:

SELECT SERVERPROPERTY('ErrorLogFileName');

Recycle the Error Log
MSDN: sp_cycle_errorlog

EXEC sp_cycle_errorlog;
GO

This causes a new error log file to be created and will prevent the error log becoming overly large on systems that do not reboot for a long time.

Are you using these features on your production server? Let me know.

Advertisements

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