Sizing the SQL Server Error Loghttps://sqldatapartners.com/wp-content/themes/crocal/images/empty/thumbnail.jpg150150Carlos L ChaconCarlos L Chaconhttps://secure.gravatar.com/avatar/5e3365b6a313f63d0ad1fd6748a46345?s=96&d=mm&r=g
Sometimes SQL Server generates so many messages you think it actually enjoys it. While there may be some other factors in creating a new log by default, it appears to me the most common scenario is when SQL Server restarts. If your systems are anything like mine, you want SQL Server to stay up and this can cause the logs to get very large and this makes finding specific message more painful than it needs to be.
To me it makes sense to separate the logs by day. EXECsp_cycle_errorlog will create a new SQL Server error log. I normally create a job that runs a midnight and creates a new error file. By default, SQL Server only keeps 6 error log files and I normally increase that number to 30.
Files To Keep
If the server has already been set up, I normally use the GUI by right clicking on SQL Server Logs and choose configure. If I am setting up the server, I have this piece of code in my scripts.
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SoftwareMicrosoftMSSQLServerMSSQLServer’, N’NumErrorLogs’, REG_DWORD, 30
Cycling the Logs
This code will create a new job to run at midnight and cycle the logs.
/****** Object: Job [Cycle Error Log] Script Date: 08/01/2014 13:31:39 ******/
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/01/2014 13:31:39 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
SQL Data Partners has been acquired by Marathon Consulting, a Virginia-based Information Technology Consulting and Digital Marketing firm. We are in the process of integrating our team and services into Marathon as of May, 2023.