3 SQL Server ERRORLOG must have configurations + 7 useful techniques

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.

funny-errors

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

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)?

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:

SQL-ErrorLog-Location-when-SQL-is-not-accessible

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>

Leave a Comment