Overview Of Cycle Error Logs

I saw this again recently, and have seen it too often in environments so I wanted to take a second to remind everyone to cycle their error logs on a regular basis. SQL Server keeps the error logs and when you reboot or restart the SQL Server services, the logs are cycled and a new one is created. Depending on how many logs you have configured for SQL Server, having this may include removal of the oldest log as well. Since many of us pride ourselves on keeping our SQL Servers up and running, reboots may be few and far between thus our logs get large in size.

When the logs grow out of control, it can require long wait times for the logs to open and to even view them. An easy way to keep this from happening is to cycle them routinely. You can easily automate these by creating an SQL Agent job on a regular basis, whether monthly, weekly, or daily.

First, you need to double check and determine how many error logs you want to maintain.

For this example, I'll be keeping one for each month and setting up a monthly job to cycle the logs. This means I will want to configure the maximum number of error logs to 12. Now, something to keep in mind is that when the server is rebooted, you will still only have 12 logs. You may end up losing a log you may want to keep. I am using 12 as an example but would suggest you have a buffer and go with a number like 16 or 20 to be safe.

Cycle Error Logs

Cycle Error Logs 

Using T-SQL

  1. USE[master]  
  2. GO  
  3. EXEC xp_instance_regwrite N 'HKEY_LOCAL_MACHINE', N 'Software\Microsoft\MSSQLServer\MSSQLServer', N 'NumErrorLogs', REG_DWORD, 12  
  4. GO  
  5. Here is the script to create the job.It simply creates and schedules an Agent job to run as system procedure called EXEC master.sys.sp_cycle_errorlog.  
  6. USE[msdb]  
  7. GO  
  8. BEGIN TRANSACTION  
  9. DECLARE @ReturnCode INT  
  10. SELECT @ReturnCode = 0  
  11. IF NOT EXISTS(SELECT name FROM msdb.dbo.syscategories WHERE name = N '[Uncategorized (Local)]'  
  12.     AND category_class = 1)  
  13. BEGIN  
  14. EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N 'JOB', @type = N 'LOCAL', @name = N '[Uncategorized (Local)]'  
  15. IF(@ @ERROR < > 0 OR @ReturnCode < > 0) GOTO QuitWithRollback  
  16. END  
  17. DECLARE @jobId BINARY(16)  
  18. EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N 'MaintenanceCycleErrorlogs',  
  19.     @enabled = 1,  
  20.     @notify_level_eventlog = 0,  
  21.     @notify_level_email = 0,  
  22.     @notify_level_netsend = 0,  
  23.     @notify_level_page = 0,  
  24.     @delete_level = 0,  
  25.     @description = N 'No description available.',  
  26.     @category_name = N '[Uncategorized (Local)]',  
  27.     @owner_login_name = N 'sa', @job_id = @jobId OUTPUT  
  28. IF(@ @ERROR < > 0 OR @ReturnCode < > 0) GOTO QuitWithRollback  
  29. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId, @step_name = N 'Recycle the SQL Server Error Log',  
  30.     @step_id = 1,  
  31.     @cmdexec_success_code = 0,  
  32.     @on_success_action = 1,  
  33.     @on_success_step_id = 0,  
  34.     @on_fail_action = 2,  
  35.     @on_fail_step_id = 0,  
  36.     @retry_attempts = 0,  
  37.     @retry_interval = 0,  
  38.     @os_run_priority = 0, @subsystem = N 'TSQL',  
  39.     @command = N 'EXEC master.sys.sp_cycle_errorlog;',  
  40.     @database_name = N 'master',  
  41.     @flags = 0  
  42. IF(@ @ERROR < > 0 OR @ReturnCode < > 0) GOTO QuitWithRollback  
  43. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1  
  44. IF(@ @ERROR < > 0 OR @ReturnCode < > 0) GOTO QuitWithRollback  
  45. EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId, @name = N 'Monthly',  
  46.     @enabled = 1,  
  47.     @freq_type = 16,  
  48.     @freq_interval = 1,  
  49.     @freq_subday_type = 1,  
  50.     @freq_subday_interval = 0,  
  51.     @freq_relative_interval = 0,  
  52.     @freq_recurrence_factor = 1,  
  53.     @active_start_date = 20111110,  
  54.     @active_end_date = 99991231,  
  55.     @active_start_time = 0,  
  56.     @active_end_time = 235959  
  57. IF(@ @ERROR < > 0 OR @ReturnCode < > 0) GOTO QuitWithRollback  
  58. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N '(local)'  
  59. IF(@ @ERROR < > 0 OR @ReturnCode < > 0) GOTO QuitWithRollback  
  60. COMMIT TRANSACTION  
  61. GOTO EndSave  
  62. QuitWithRollback: IF(@ @TRANCOUNT > 0) ROLLBACK TRANSACTION  
  63. EndSave: GO  

If you are not doing this in your environment, please spare a little time to set it up. It’s a simple maintenance task you should not neglect.