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.

EXEC master.sys.sp_cycle_errorlog;

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: