SQL Server Reference Guide

There are several chores that a Database Administrator (DBA) has to do, and among them is to check the logs. Over the years I've developed several ways to do this, some of which work well, others that take more work and are more prone to error. I've found one that works very well, so in this tutorial I'll show you how I put the process I'm using now to work.

Before we get started on that process, let's look at how the logs function within the database system. SQL Server creates recordings of certain events and all errors for the engine, the SQL Server Agent, and in SQL Server 2005, Reporting Services. It also creates entries in the Windows Application Event Log for a smaller subset of events and most of the major errors. While you certainly should take a look at these logs when a problem arises, you should also review them when things are going well. In fact, I recommend that you review these logs every day for at least your production systems. It's one of the basic checks you should do.

The Windows Application Event Logs are controlled by settings in the operating system, starting with the first time to start the system (ever) until you clean them up. Many system administrators set a size limit for each Windows Event Log, and then set an option to cycle over the older events as the Log reaches that size. While that makes for easy administration, if the system encounters a lot of errors, you could have the log cycle out the errors you haven't had a chance to review yet. The better option is to review the Windows Event Logs (all types, not just the Application Log) daily and truncate them only after you've read through them and saved them off to another drive.

The SQL Server Logs are created each time the SQL Server service is started, for each Instance of SQL Server installed on the computer. It creates a "Current" log and cycles that 6 times, giving you a total of seven logs.

Sometimes when you're troubleshooting, seven logs aren't enough. You can actually change the number of logs that SQL Server will retain in SQL Server 2005 in SQL Server Management Studio by opening the Management object, and then right-clicking the SQL Server Logs node. Then select the Configure option, and then the Limit the number of error log files before they are recycled check box. Just type in the number of error logs you want in the Maximum number of error log files and press the OK button.

You can also edit the registry of the server before you start SQL Server and change the number of logs there. Just find the HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer Hive and edit the NumErrorLogs RegKey to the number you want. You can go as high as 99.

Checking the logs is pretty simple, really. Microsoft gives you tools within both SQL Server 2000 and 2005 to read the SQL Server Error Logs, in version 2005 you can include the Windows Event Logs (almost all of them, not just the Application Log) as well as the SQL Server Agent Logs in one view. I really like that feature, because it allows me to correlate things by time, items on the server as well as events that happen in SQL Server. Enterprise Manager also lets you look at the SQL Server Logs, but not the Windows Logs, although you could open those in Windows.

So with those great tools available in SQL Server, at least in the later versions, why not just use the tools that came with the product to review the logs?

Well, that certainly works well enough, and there's nothing wrong with that approach — I normally fire up the tools and check out the logs this way when I'm evaluating a new system. But while this works for a single system when I have lots of time, it doesn't scale very well on dozens or hundreds of servers with multiple instances. And there's a bigger need. Sometimes the server is down — the services won't start, and I'm the hot seat. I need something that works no matter what.

So whenever I'm faced with the problem of scale and cross-platform requirements, I turn to automation, and in particular, scripting. And one of the easiest scripting languages for a DBA to learn is PowerShell. I've talked about PowerShell before, and I've shown you how it can be used with SQL Server in lots of ways. Now we'll take PowerShell and apply it to the problem of reading SQL Server Error Logs and Windows Event Logs on multiple systems and when the SQL Server isn't started.

I won't cover the process for wrapping this script with error messages, or logging this process, or even how to iterate through multiple servers or logs. The reason is that I've covered those concepts elsewhere, and I want to focus on the parts you can use to do the actual work — the engine, so to speak. If you'll refer back to those introductory articles, you'll see where I've detailed those processes and then left a block called "Do Some Work," we'll focus on the items you would put there.

There are a few ways to handle this chore, depending on how you want to structure your day, or how many servers you have. The first method is immediate — you get to see the output when you run the file. When I have a few servers or I'm doing some investigation, this is the method I use. Another method is to bundle up the work and handle it in bulk. I use this method when I have a lot of servers or I review the logs at a set time. Still another method is to use reporting, which I use when I have a really large amount of servers or I want multiple people to see the results. We'll discuss each method and then you can craft your scripting to the choice you want.

Let's start with the immediate approach. If you have PowerShell installed, you can try this out on your own test system. First, let's go after the Windows Event Logs. For SQL Server, I'm interested in the Application type, but you can also use this command to read the Security and System logs as well.

Let's examine the whole line, which you can run on your own system, and then I'll break down the parts for you:

$WindowsLog = get-eventlog -logname application
$WindowsLog | select-string -inputobject {$_.eventid, $_.source, $_.message} -pattern "failed"

If you've read the PowerShell series so far, you already know that the $WindowsLog string just creates a new variable — a placeholder. The equals sign next to it means "take everything to the right of this and assign it to the variable I just made." So far, so good. Next, we use the get-eventlog command to open the event logs and read them. We have one qualifier here, because the get-eventlog command wants to know which log you're after — in our case, the application log.

So now we have a variable called $WindowsLog that has the contents of the application log in it. You can see that by typing that variable all by itself and pressing ENTER. Try that now.

OK, so just moving the event log from one output to another isn't that great. But you can start to see the real power when you look at the next line of the script. We start with the same variable name again, $WindowsLog, but this time we send the output of that variable (the logs) to another command, using the "pipe" symbol: |

The command we use here is select-string, which finds and displays the strings we want to see. In this case, it's the word "failed", but we might also want to run that with the word "error" and others. Let's stick with "failed" for now. The select-string command has several parameters, but since we're sending an object (the log file variable), we need to tell it to use that to search across. We can do that using the parameter –inputobject. Following that, we use a "where clause" to encase what particular parts of the log file we want to see. We do that by surrounding the next bits with braces {}. Inside those braces we use a few wildcards, which are a dollar sign, and underline, and a period. Now comes the real trick: we specify the columns of the log file we would like to look for. In this case, we want to look in the "message" bit, but we also want to see the ID of the log entry that failed, as well as the "source" column, which has the actual message in it.

We're almost done — now all we have to do is tell select-string what we want to find. We do that with the –pattern parameter, followed by the string we want to search for enclosed in quotes.

That takes care of the Windows Event Logs — but how do we read the SQL Server logs, and what do we do if the SQL Server services are down?

No problem. The interesting thing about the SQL Server Error Logs (which actually contain more than errors in them) is that they are really just text files on the server. We can read them with anything that can read ASCII text. They are located (by default) in the instance of each server, and in the case of SQL Server 2005 they are normally stored at:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\

If that directory isn't there, or you're using SQL Server 2000, just look for a file on your system called ERRORLOG. Open it with Notepad and make sure it's the SQL Server log file, and note the directory. All of the logs, including the SQL Server Agent logs, will be stored there.

Luckily, PowerShell can read text files without any trouble at all. In fact, we can skip the step of creating an object at all, and just use the select-string command directly against the log files. Let's look for the word "error" this time, once again with select-string:

select-string "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG" -pattern "error"

Easy Peasy. And of course, you can use those iteration techniques I showed you earlier to read through all of the logs, not just the current one. The SQL Server Error Log files have numbers after each one, with the ERRORLOG file and no number being the current one.

But what if you want to batch up the files and read them all later? That's simple too — we can just add the out-file command to send the results of whatever we do to the hard drive. It takes a file-name as a parameter. Let's do that with the previous statements. I'll send the results to my temp directory:

$WindowsLog = get-eventlog -logname application
$WindowsLog | select-string -inputobject {$_.eventid, $_.source, $_.message} -pattern "failed" | out-file c:\temp\WindowsLog.txt
select-string "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG" -pattern "error" | out-file c:\temp\SQLLog.txt 

You can probably see already how you could make a report from this. I currently take these files and a little HTML magic, combining them all into one report. You could even use Reporting Services with these text files as a source to create error reports.

This should get you started. Depending on how you want to check your systems, you can use the commands I've described here to make a great exception-based reporting system for multiple servers.

InformIT Articles and Sample Chapters

Need another PowerShell into other than mine? My feelings aren't hurt! Check this out.

Books and eBooks

When you're trying out PowerShell, a great book is your best friend. We have lots of them, and this one is quite useful.

Online Resources

The definitive reference for PowerShell is here: http://blogs.msdn.com/powershell/archive/2007/05/11/free-powershell-book.aspx.