Tuesday, June 5, 2012

Create New Error Log or Recycle Error log in SQLServer

SQLServer - Every time you restart the SQLServer a new error log will be created. The old error will be renamed as ERRORLOG.1. In most of the production environment the SQLServer will be restarted rarely and you will notice a large ERRORLOG.

In order to recycle or create a new error log you no need to restart the SQLServer.
You can recycle the error log using below command.

EXEC sp_cycle_errorlog
GO

You can limit the number of error log files before they are recycled.
1. In Object Explorer, Click the plus sign to expand Managment.
2. Right-click on the SQLServer Logs folder and select Configure.
3. In the Configure SQL Server Error Logs dialog box, you can enter maximum number of Error logs.

SQLServer Agent also has an error and it will be recreated every time you restart SQLServer Agent. The new error log will be generated as SQLAGENT.OUT and old error log will be renamed as SQLAGENT.1...etc. The SQLServer Agent can maintain up to 9 SQLServer Agent Error logs.

You can recycle the SQLServer Agent Error log using below
EXEC sp_cycle_agent_errorlog
GO

It is always good practice to setup a job to recycle both SQLServer and SQLServer Agent error logs.

Following below steps you can reanme SQLServer Agent error log
1. In Object Explorer, Click the plus sign to expand SQL Server Agent.
2. Right-click on the Error Logs folder and select Configure.
3. In the Configure SQL Server Agent Error Logs dialog box, enter the new file path and file name and click OK

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com/