SQL Server Reference Guide

Nobody likes to get an error in a computer system. For a user, information about an error is presented on the screen they are looking at, and if possible they are shown information needed to correct the issue. But in the case of a platform system like SQL Server, you aren’t sitting in front of the program while it’s running on the server. You need a way to instrument the system so that you can look at the information about any errors that occur not only later, but from another location.

One of the most frustrating things is having a problem and not having all of the information you need to solve it. There are three things that you need to know about computer errors:

1. That you had an error
2. What the error is
3. What you can do about it

In addition, you’re not just interested in errors. You’re interested in when the server started, system messages, and other information you can use proactively to prevent errors.

Microsoft includes a robust error and information logging system for SQL Server. You need to know how to locate and review these logs, not just on a reactive basis, but on a proactive basis as well. In today’s tutorial I’ll show you where the logs are in SQL Server and how you can read them inside the tools these servers provide as well as using any text editor or parser. I’ll focus on how you can find out if you have an error, how to get the text, and how to find information on solving them, but you should also know that other types of information exist in these logs.

SQL Server Error Logs

I’ll begin with what the error logs are and how you can use them. I’ll then show you how SQL Server interacts with the Windows operating system. After I review that information I’ll demonstrate accessing the logs in each version.

The error logs in SQL Server don’t just contain errors. They contain a lot of information about SQL Server in general, from startup and shutdown events, and actions like backups. By default, there are seven log files. SQL Server creates a new one each time it starts, cycling through these logs on each startup, moving the oldest one off and replacing the most recent with a new one. You can change that number to have more logs if you know you’re going to start and stop the server often, especially during development or testing. Keep in mind that if you reboot your server multiple times for a non-SQL Server issue and then you need to look at the logs for an earlier message in SQL Server, you may lose it if you don’t set this number higher. This isn’t a common issue, but it is something to be aware of.

There are multiple sets of these "error" logs, one for the engine or server and one for the SQL Server Agent, another for Database Mail and so on.

SQL Server also records various events in the Windows Event Logs. The primary log you’ll use for SQL Server is the Application log, although the System log is also important, since SQL Server depends on many Windows functions to operate. The information you find in the Windows Event Logs are a subset of the information you find in the SQL Server error logs, so you’ll probably user the error logs more often. Your system administrators can use the Windows Event logs for messages that are more pertinent to the overall system, or you can use your favorite tool that reads event logs to parse through these for an integrated system evaluation. I’ll show you how to do that using PowerShell at the end of this article.

The Error Logs in SQL Server are actually text files. They are normally found at:

Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n

Replace the “N” with the version of SQL Server and the error log number. But most folks use graphical tools to read them.

Error Logs in SQL Server 2000 and earlier

In SQL Server 2000, the primary management tool is Enterprise Manager. Once you open that tool, you can connect to a server, which may be the same machine as where you are running the tool or on another system. In my case the tool is on the server.

Once you connect to a server, drill down to the Management | SQLServer Logs object to look at the first set of logs.

Earlier I mentioned that you might want to change the number of logs that SQL Server keeps. If you right-click the SQL Server Logs object and select the Configure... item from the menu that appears, you’ll be able to set the number of logs you want to keep.

The SQL Server Agent log is located in a different place in SQL Server 2000. Staying under the Management item in Enterprise Manager, right-click the SQL Server Agent object and select the Display Error Log... item from the menu that appears.

This display is a little different, although it’s still reading the same kind of file underneath. The log in this view is automatically filtered. For that reason, When I’m using SQL Server 2000 or earlier I normally like to read both logs outside of the tool, using a text parser. I’ll show you how I do that in a bit.

Error Logs in SQL Server 2005 and later

SQL Server 2005 and later use a different tool for management, called SQL Server Management Studio or SSMS. This newer tool also has a far better interface for showing and managing the logs graphically.

Open SSMS and connect to a SQL Server Instance. You can also use SSMS to connect to SQL Server 2000, but the log contents are slightly different between the two versions, so I’ll stick with the later versions for now.

One you have connected to a server, open the Management | SQL Server Logs item from the Object Explorer pane and click on the current Error Log.

Figure 1

Now just double-click that item.

Figure 2

This view is much better for the administrator than the earlier versions. Everything is in one place, including not only the SQL Server Agent logs, but Database Mail and even the Windows Event logs. You can check each box for the logs you want to see, and the entries they contain pile into the right-hand view, ordered by date and time by default. This is a great way to correlate events by looking at the system as a whole, rather than having to peruse each log separately.

Figure 3

Not only do you have the ability to “fold” all of the items together, at the top of the panel you can export the information to a file, or even a comma-separated values file that you can import into another program. You can also open a log file in this view as well.

You can also filter the results, showing only the information you want to see.

Figure 4

The Windows Event Logs

Since the first server operating system from Microsoft, the Windows system has used the Event Log program to record and view log entries from at least three sources: System, Security, and Applications. In fact, it isn’t difficult to code your own log that will be placed in the same view. In more recent Windows operating systems, you’ll see even more logs.

The System log contains entries dealing with the entire system. Security logs have various kinds of information regarding security events, and may have more or less items depending on your computer’s auditing settings. The Application event log is used to log application-specific events, such as Internet Information Server or SQL Server.

You can find the Event Viewer tool under the Start menu in Windows, under the Administrative Tools option.

There are a few things to look at right away, as it pertains to SQL Server. The first is the Source column. For SQL Server, there are several sources of the entries, most including the name of the instance of your server. Since my server has multiple instances on it, one for SQL Server 2000 and another for SQL Server 2005 and yet another for 2008, I have entries for both of those service names.

Figure 5

But there are other sources that have to do with SQL Server as well, such as the Full-Text Search service and the Agent services for all versions. You can determine the source entries by looking at the service names on your system with the NET START command at the Windows command-line.

The next items to look for are the icons, in particular the caution and stop signs. A caution icon means that the event was unexpected, but not fatal. A stop sign means that something had to terminate a process.

Interpreting Entries in the Error and Event Logs

I’ll return to the SQL Server error logs for a moment and explain their structure. Here are the first few lines from one of my SQL Server 2000 logs (This format holds true for the later versions of SQL Server as well):

2008-12-10 09:48:19.90 server  Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) 
   May 3 2005 23:18:38 
   Copyright (c) 1988-2003 Microsoft Corporation
   Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2007-02-10 09:48:19.90 server  Copyright (C) 1988-2002 Microsoft Corporation.
2007-02-10 09:48:19.90 server  All rights reserved.
2007-02-10 09:48:19.90 server  Server Process ID is 1448.
2007-02-10 09:48:19.90 server  Logging SQL Server messages in file
 'C:\Program Files\Microsoft SQL Server\MSSQL$I2K\log\ERRORLOG'.
2007-02-10 09:48:20.64 server  SQL Server is starting at priority class
 'normal'(1 CPU detected).
2007-02-10 09:48:21.15 server  SQL Server configured for thread mode processing.
2007-02-10 09:48:21.46 server  Using dynamic lock allocation.
 [2500] Lock Blocks, [5000] Lock Owner Blocks.
2007-02-10 09:48:21.95 server  Attempting to initialize Distributed Transaction Coordinator.
2007-02-10 09:48:26.70 spid3   Starting up database 'master'.
2007-02-10 09:48:35.29 spid3   Server name is 'SQL\I2K'.
2007-02-10 09:48:35.32 server  Using 'SSNETLIB.DLL' version '8.0.2039'.
2007-02-10 09:48:35.40 spid5   Starting up database 'model'.
2007-02-10 09:48:35.89 server  SQL server listening on 190.168.0.104: 1064.
2007-02-10 09:48:35.89 server  SQL server listening on 127.0.0.1: 1064.
2007-02-10 09:48:35.92 server  SQL server listening on TCP, Shared Memory, Named Pipes.
2007-02-10 09:48:35.93 server  SQL Server is ready for client connections
2007-02-10 09:48:36.04 spid8   Starting up database 'msdb'.
2007-02-10 09:48:36.04 spid9   Starting up database 'pubs'.
2007-02-10 09:48:36.14 spid10  Starting up database 'Northwind'.
2007-02-10 09:48:36.15 spid11  Starting up database 'sqlbpa'.
2007-02-10 09:48:40.53 spid5   Clearing tempdb database.
2007-02-10 09:48:55.90 spid5   Starting up database 'tempdb'.
2007-02-10 09:49:01.04 spid3   Recovery complete.

After the header, which contains the name and version of SQL Server and other general information, the first entry is a date-time stamp. That’s followed by the source of the entry. If it is a system-wide entry, it’s the word "server," and if it has a spid (SQL Process ID) number, it was a process that was running under a specific login. You can find out more about the spid (although they change throughout sessions) by looking in Enterprise Manager in version 2000 or Management Studio in version 2005 and higher under the Activity object or by running a query against the sysprocesses system tables.

The next column is the message. You can see that the system explains what it is doing from the earliest point to the latest, so you’ll need to scroll down to find out the latest happenings on the system. The earlier entries are useful as well because they tell you about the startup process for the server, and whether the databases "recovered" on startup.

Since I have multiple systems, I normally use PowerShell to view the logs, primarily because I’m doing a lot of system maintenance using scripts. Once a week or so I review the entire log set, but I use the PowerShell script I’ll explain in a moment to look through the files for the words "error," "failed" and so forth. I have a script that runs this each morning to send me an e-mail with the results of that scan.

Now that you know the format of the logs and how to read them both from the operating system and SQL Server, what do you do if one of them shows an error or caution icon?

First, read the error carefully, and don’t stop with the error condition itself. You need to read backwards, from the line where you see the error through the events that preceded it. This is where the integrated view from SSMS becomes especially valuable. If you check both the SQL Server logs and the Windows Event Logs boxes, the tool will place the items in the time sequence they occur from both sources. Just recently I found an error in SQL Server in initializing a data file, and just before it a Windows Event error on a storage device. Putting those two pieces of data together helped me determine that the system had a faulty spot on a hard drive, quickly solving the problem.

As you go through the logs, note any errors or cautions earlier in the information. In fact, I often copy this out into a separate Notepad file to read them together. It’s interesting to find the patterns that jump out at you.

From there, Books Online has a list of error codes that you should reference to find more. Start here for that process, and read the descriptions and explanations carefully. You’ll find that rather than explaining what you need to do to fix a particular problem, Books Online and other official documentation often simply explain the problem and what causes it. That may not seem helpful [md] but in fact it is. Understanding why a particular error occurs helps you find what caused it on your system so that you can correct it.

Of course, when you’re in the middle of an error condition, you’re often not interested in lots of background [md] you simply want your problem solved. For that part of the investigation, I recommend you start with the SQL Server Community. These are technical professionals who give unselfishly of their time to help others out. You can find a good launching point for the SQL Server Community here. Be a good citizen [md] be polite, do your own research first, explain your problem carefully, and understand that the people you’re asking are volunteers. They don’t have to help you, so make sure you treat others professionally. And when you’ve got some time, jump into those forums yourself and answer whatever questions you can. You probably know more than you think, and you certainly know something that can help others. Be sure and give back to the community that helps you.

From there, you should open your favorite search engine and look up your error message. However [md] a word of caution here. don’t stop on the first hit you find for your error, even if it looks like the person in the newsgroup solved their problem. Continue looking through the other references to ensure that the problem they had is the same one you did, or that the KB article is for the version and service pack you have. You can generally trust many Microsoft posts, and those from the “MVP’s” [md] Microsoft’s Most Valuable Professional champions. They will normally display an “MVP” badge on their sites, they post frequent blogs, and you’ll see them in many of the Forums. They also do quite a bit of technical speaking, so at conferences and other events make sure you seek them out.

It should be mentioned that there are various vendors and partners that you can get to help you on a paid basis, and I recommend you leverage the community once again in selecting one.

Reading SQL Server Error Logs using PowerShell

The graphical tools are great, especially in version 2005 and higher. It presents the information in an easy-to-read format, and you can filter, sort and export the information. But if you have more than one server, you have to look at them all one at a time, which can be a real time-sink. These days I’m using PowerShell from Microsoft to script out this task. Starting in SQL Server 2008, you have access to the server as an object, but you can also just load the “Server Management Objects” program interface to get similar behavior against any version. I have a full set of articles on using PowerShell with SQL Server here if you’re unfamiliar with this great new technology.

After you read that article, you can insert the following lines in your more comprehensive script. All you need for this to work is to have the SMO libraries installed where you run it, and those are already there if you have any of the SQL Server 2005 or higher client tools or server software installed. Replace YourServerName and YourInstanceName with the name of your server and instance for this to work. Also, you can change the “Error*” part to another text string if you want to search for something other than errors:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$machineName = “YourServerName”
$instanceName = “YourInstanceName”
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$machineName\$instanceName"
$sqlServer.ReadErrorLog() | where {$_.Text -like "Error*"}

Reading Windows Event Logs using PowerShell

I like to scrub through the Windows Event logs using PowerShell as well. To find all of the SQL Server information, you don’t have to load anything special; you just need to run the command on the server where SQL Server is installed:

get-Eventlog application | where-Object {$_.source -like "MSSQL*"}

You can change the “application” part after get-EventLog to “system” or “security” to read those logs as well, and change the “Message to Search*” string below to find a message by string regardless of the application that generated it:

get-Eventlog application | where-Object {$_.Message -eq "Message To Search*"}