sql server error log

How to Analyze the SQL Server Error Log

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!

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:

xkcd rtfm
Image / License

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.

  1. SSMS
  2. T-SQL
  3. File System

SQL Server Management Studio

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

ssms object browser error log

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

Log file viewer

You can filter the log as such:

filtered log

The output will look like this:

filtered log output

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:

Output:

sp_helptext on sp_readerrorlog

Here are some examples of querying the error log:

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.

startup parameters

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

Structure of Error Log

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

beginning of error log

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:

  1. Cycling the log
  2. 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:

Here are the log files before running the command:

error log before cycling

…and after:

error logs after cycling

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.

use notepad++ to search all in current document

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

Did you find this helpful? Please subscribe!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.