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
Use the GUI of SSMS (or SSDT in SQL Server 2012) to recycle it manually:
(Right-Click on ‘Error Logs’)
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.)
EXEC dbo.sp_cycle_agent_errorlog ;
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:
Recycle the Error Log
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.