SQL ERRORLOG is not optimally configured fresh out of the box.
Here are a few ideas on how to make it better.
There are few issues of how SQL Server sets up ERRORLOGs by default:
1. Only 7 log files are kept by default.
2. Files grow indefinitely.
I (usually) do a few things to make ERRORLOGs more manageable:
1. Change how many ERRORLOG files are kept from 7(default) to 99.
2. Cycle log file at midnight, so each day gets a new log file.
Why?
1. When SQL server service starts – a new ERRORLOG is created and the oldest log file is lost. If you restart SQL Service a few times – you can overwrite log files with valuable history.
Normally ERRORLOG files do not take a lot of space. Having 3 months of error logs is usually enough. It can be less if you want, but I want all the history I can have.
2. Indefinite log file growth is a problem. If not cycled to a new file, ERRORLOG grows without the limit. And only when SQL Service is restarted log gets written to a new file. When there is a problem with the SQL Server – opening a large text log file is a problem. And Notepad sucks at it.
When I finally open the sucker, real errors – the ones I want to know find – are sandwiched between a bunch of entries that are not important. So now I have to search through the ton of useless data and hope I don’t miss entries that are relevant. A modern-day ‘Looking for a needle in a haystack’.
To avoid this when I take over managing a new SQL Server I run scripts below: step 1 and step 2:
(You can do the same with SSMS UI or PowerShell. I like to configure servers using TSQL as option 1 and PowerShell as option 2).
SQL Server Errorlog Management Best Practices
------------------------------------------------------------------
--Step 1 - keep 99 ERRORLOGs
------------------------------------------------------------------
USE master
GO
EXEC xp_instance_regwrite
N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'NumErrorLogs'
, REG_DWORD
, 99
GO
------------------------------------------------------------------
--Step 2 - create SQLAgent job to cycle ERRORLOGs nightly
------------------------------------------------------------------
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories
WHERE name=N'[Uncategorized (Local)]'
AND category_class=1)
BEGIN
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
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job
@job_name=N'SQL Error Log Cycle',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Cycle SQL Error Log (daily).',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id=@jobId,
@step_name=N'Cycle SQLError log',
@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 sp_cycle_errorlog;',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job
@job_id = @jobId,
@start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id=@jobId,
@name=N'Run at midnight',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20120110,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @jobId,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
EndSave:
GO
------------------------------------------------------------------
--Step 3 - Check enabled trace flags
------------------------------------------------------------------
--get all trace flags that are enabled globally
DBCC TRACESTATUS(-1);
--get all the trace flags that are enabled for the current session
DBCC TRACESTATUS();
------------------------------------------------------------------
--Step 4 - Disable successful LOG backup entries from ERRORLOG
------------------------------------------------------------------
--set trace flag 3226
Trace flag idea came from Glenn Barry’s blog post.
<yellow box>
Trace flag 3226
By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.
With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.
http://msdn.microsoft.com/en-us/library/ms188396.aspx
</yellow box>
I am tempted to use the same log rotation method for cycling SQL Agent logs, but I am yet to run into an issue where this would have been helpful. Maybe it’s better to run this job weekly (instead of daily)?
------------------------------------------------------------------
--Get SQL ERRORLOG location
------------------------------------------------------------------
SELECT SERVERPROPERTY('ErrorLogFileName')
------------------------------------------------------------------
--Get SQL Agent ERRORLOG location
------------------------------------------------------------------
DECLARE @sqlAgentPath NVARCHAR(255)
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
,N'ErrorLogFile'
,@sqlAgentPath OUTPUT
,N'no_output'
SELECT @sqlAgentPath AS sqlAgentPath
------------------------------------------------------------------
--Cycle SQL ERRORLOG
------------------------------------------------------------------
sp_cycle_errorlog
------------------------------------------------------------------
--Cycle SQL Agent error log
------------------------------------------------------------------
sp_cycle_agent_errorlog
Misc items:
Default SQL ERRORLOG location:
C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG
When SQL Server service is not running or not accessible you can find ErrorLog without TSQL:
Sometimes it may make sense to eliminate some errors from the SQL Error log here is one method you can use.
Here is a good screenshot about the rest of SQL Server error.
<yellow box>
I’d love to know how to make SQL Error management better, please let me know in the comments if you know-how!
</yellow box>