The SQL Server Error Log is one of the most useful thing to troubleshoot database server issues. System errors get logged here along with a host of other useful information.

Read on to see how to understand, analyze, and manage the SQL Server Error Log…

Why Should You Care About Reading This Log?

So why should you care at all about the error log? While it is true that the Windows Events Logs contains many useful bits of information, the more specific errors get written to the SQL Server error log. Are you troubleshooting issues on your SQL Instance? If so then reading and understanding this log will immensely help.

The SQL Server Error Log:

Wealth of information about the SQL Instance
A great source of troubleshooting information
The answers are often in the logs – you just have to read them

RTFM – The Truth is Out There!

There is a very old retort that has been around since the early Unix days for when n00bs ask questions on message boards: RTFM. Developers can be harsh spoken and system administrators can be thick skinned. Learning a programming language, for example, is a tough task. There become a lot of elementary questions asked and some people get turned off by the curt responses. The truth of the matter is that there is no substitute for reading the documentation.

Professionals who contribute to forums don’t want to do your homework or provide free consulting for such basic things. You really need to put in some work first and then present what you’ve got and ask your question.

There is no substitute for reading logs to understand behavior

Reading logs is not sexy. For non-robots it can be boring and tedious. However the SQL Server error log is a goldmine of useful information to troubleshoot your SQL Server Instance. Are you getting crash dumps? Deadlocks? Connectivity issues? Services crashing? Corruption? All of these can be discerned from analyzing the error log. You would do well to have this skill!

Here’s my favorite xkcd comic showing what happens when you don’t read the documentation:

Anatomy of SQL Server Error Log

Now let us examine the parts which comprise the error log.

How to View the Error Log

There are a few ways to find and view the error log.

SSMS
T-SQL
File System

SQL Server Management Studio

In SSMS you can find it in the object browser. Here it is:

From here you can double click on the current error log and open it up. You will see something like this:

You can filter the log as such:

The output will look like this:

Go ahead and export to a file. Play around with it and get familiar with reading these logs.

T-SQL

SQL Server 2005 introduced a new undocumented command: sp_readerrorlog. This allows us to query the error log which can be powerful.

A word of caution – undocumented means Microsoft can change or remove it anytime and does not provide support for this command.

It is a wrapper around an extended procedure called xp_readerrorlog. You can know this by querying:

--show definition of procedure
exec sp_helptext sp_readerrorlog

1 2	--show definition of procedure exec sp_helptext sp_readerrorlog

Output:

Here are some examples of querying the error log:

exec sp_readerrorlog; --show the full most recent error log
exec sp_readerrorlog 1; --choose the next youngest log (default is 0 meaning current)
exec sp_readerrorlog 0, 1 --show current error log with log file type (same as the first command)
exec sp_readerrorlog 0, 1, 'error'; --grep current error log for keyword
exec sp_readerrorlog 0, 1, 'error', 'logging'; --an additional search parameter to refine

exec sp_readerrorlog; --show the full most recent error log

exec sp_readerrorlog 1; --choose the next youngest log (default is 0 meaning current)

exec sp_readerrorlog 0, 1 --show current error log with log file type (same as the first command)

exec sp_readerrorlog 0, 1, 'error'; --grep current error log for keyword

exec sp_readerrorlog 0, 1, 'error', 'logging'; --an additional search parameter to refine

File System

My preferred method of accessing and analyzing the error log is by way of the file system.

How do we know where the file is? By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files. Sometimes a DBA will move it and we need to look harder. You can always just search the file system in Windows Explorer for “ERRORLOG”.

Using the SQL Server Configuration Manager:

You see the services running for SQL Server. Right click on the service for your instance and look at the startup parameters. This is the path where your log files are likely to be.

Additionally you can query the log location in T-SQL:

--query error log location
SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log file location';

1 2	--query error log location SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log file location';

Structure of Error Log

Besides errors you can learn a lot about an instance by reading some of the beginning lines.

Just by reading the first 35 lines I can discern:

The exact version and build of SQL Server
The OS version and build
Time zone
Authentication mode
Default locations for log files
CPU recognized
Memory allocated
Collation
IFI

If you knew nothing about a server this can tell quite a lot!

How to Manage the Error Log

There are 2 options to be aware of when managing the error log:

Cycling the log
Controlling the size of the log

By default the error log will create 7 log files to cycle through. When you add an eighth it drops the oldest one and renames the rest accordingly. The first “ERRORLOG” is the current one always.

Every time the SQL Server service is restarted a new error log file is created

Cycling the Error Log

We can cycle the error logs (meaning create a new one and push the last out of the queue) by using sp_cycle_errorlog:

--cycle the error log
exec sp_cycle_errorlog;

1 2	--cycle the error log exec sp_cycle_errorlog;

Here are the log files before running the command:

…and after:

NOTE: I accidentally ran it twice – that is why there are 2 new logs created.

Under the covers this command runs dbcc errorlog.

Controlling the Size of the Error Log

To change the size that an error log file can be you need to make a change in the registry. The Microsoft documentation shows the key we are interested in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs

PROTIP: Always back up the registry BEFORE making any changes!

Understanding the Error Log

Some of these logs can get very big and appear hard to analyze. You need to know the important bits to examine and search. I like to do it this way:

Find and open the Error Log in Notepad++ (or your favorite text editor)
Ctrl + F to enter a search term
Click “Find All in Current Document”

Now you can easily analyze the file. Double click on the line in the output to go straight to it in the file.

Notice you can use regular expressions – they are your friend when searching text.

I like to search out the following terms on the regular:

severity:
crash
dump
deadlock
victim
fatal

Searching on “severity:” tends to show useful output

Don’t just search for “error” because you will get a lot of false positives that you may not care about. I have the best luck with “severity:” because I can evaluate what is important and it reduces noise.

Well there you have it. Get your hands dirty in the log and you will level up your skills.

Thanks for reading!

If you liked this post then you might also like: Stop and Think Before You Shrink – Shrinking a SQL Server Database