MS SQL Server has a habit of sprouting enormous appendages in the form of log files. Like the infamous Tree Man from Indonesia, sometimes this can be a situation that gets out of control. The SQL error log is one of these sources of pain, but there are a few tricks to pull out of your sleave that’ll save the day.
The following solutions should give you a few options in your travels - they should work with SQL 2005 onwards (screen shots are taken from SQL 2008, although after a quick check they appear identical)
Setting the number of general log files to keep in rotation
Microsoft SQL server by default keeps 7 general SQL log files. You may want to change this number to a larger figure to keep a better history of what has happened (or keep less to save space).
- Open SQL Server Management Studio
- Select “Management” on the left hand pane
- Right click the “SQL Server Logs” folder and select “Configure”
- Select “Limit the number of error log files before they are recycled” check box
- Enter the number of error logs you want to keep in rotation in the “Maximum number of error log files”
Specifying what errors to log and where the Log is stored
Another option at your disposal is limiting the events that SQL server logs. To do this simply follow the bouncing ball.
Open Microsoft SQL Management Studio
Select “SQL Server Agent” on the left hand pane
Right click the “Error Logs” folder and select “Configure”
Either change the path to the log file, or specify what events you want to log (as per below – this is MSSQL 2008, but it is identical in 2005)
Creating a SQL job to rotate the SQL Job logs
There is a nice little built-in stored procedure that will rotate the SQL Job error logs.
This will cycle your error log allowing you to delete it or archive it to clear some space. By default SQL server keeps 10 SQL Server Job log files, so this means the current log file, plus 9 past. After the 9th one, the log gets deleted by SQL Server. This means that if you recycle the log regularly, that the space used will usually never become an issue.
It is even easier if you build this into a SQL job so that it happen automatically at a set interval that you control. I’ve created a sample script that will create the job called “Recycle SQL Error log” for you below, however you’ll then need to set up the schedule that it will execute under.
USE [msdb] GO BEGIN TRANSACTION DECLARE @Output INT SELECT @Output = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @Output = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @Output <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @Output = msdb.dbo.sp_add_job @job_name=N'Recycle SQL Error log', @enabled=0, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'SQL Job that will recycle the error log by calling the sproc ''sp_cycle_errorlog'' ', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @Output <> 0) GOTO QuitWithRollback EXEC @Output = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Call the Recycle SPROC', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC master.sys.sp_cycle_errorlog;', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @Output <> 0) GOTO QuitWithRollback EXEC @Output = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @Output <> 0) GOTO QuitWithRollback EXEC @Output = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @Output <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: