SQL Server Reference Guide

This is part two of a series that I’m covering as a monitoring exercise for longer-running (not necessarily performance-related) activities. You can find Part One here.

To recap, I’m looking at monitoring two broad categories of activities on my server:

• Maintenance — From how long each backup takes to index reorgs and rebuilds, this information is invaluable.
• Jobs and Scheduled Tasks — Most all of us have SQL Server Agent Jobs, and developing a schedule of how long they are running is also useful. This bucket also includes things like replication, Database Mirroring on so on.

Of course, this is in addition to the monitoring I normally do for my queries and hardware and platform bottlenecks to determine how tune my server. The monitoring for these longer-running actions (such as backups) is primarily to facilitate server planning, capacity management and even disaster recovery efforts.

I mentioned in the first tutorial in this series that you have a couple of options for this information — you can look at it “real time,” meaning just running whatever query you want and looking at the data, or you can store the data for historical analysis. For my money, it makes more sense to store the data, and I’ll store it in SQL Server. In fact, I’ll store it in the SQL Server Central Management System (SQLCMS) that I created in another series of articles that you can read here on InformIT.

I also mentioned that there are multiple ways of collecting the data:

• Querying the system databases and tables in SQL Server
• Querying the system functions and stored procedures in SQL Server
• Querying the Dynamic Management Views (DMV’s)
• Accessing the Performance Monitor (or Windows System Monitor) counters and objects
• Using the Windows Management Interface
• Accessing the Windows Event Logs
• Reading the SQL Server Error Logs (lots of ways to do this, from T-SQL functions to text file reads)
• Reading other system log files or mechanisms

In this tutorial I’ll show you an example of a few of these methods, so that you can develop your own.

Agent History Example — Using SQL Server System Tables and Views

I’ll show you the example query that I have that uses the SQL Server System Tables and Views to grab data. You can find more examples in my series on performance tuning, Backups and Recoveries, and the SQLCMS. For instance

The items to pay attention to here are that I had to join a couple of tables to get this information — it gets even more difficult if you’re using a Maintenance Plan created by the Wizards. That information is involved in different joins based on the version of SQL Server you are running. This is also a bit of a challenge with Replication — there are multiple tables there as well.

But not to despair — there are lots of tables, functions and views you can use to find this information. Most everything that deals with the SQL Server Agent is in the msdb database, and a quick web search reveals the queries you can use. As always, research anything you find on the web to ensure that the query is safe and accurate.

Notice also the way I’ve gathered the time information for this query — separating out the date formats as needed, because of the kind of data this query returns. That brings up another good point. To the best of your ability, lay out all of the things you want to track, and then an example of the results you’ll get from your chosen collection method. Then ensure the target database can handle the type of data that you need to store. Another option is to use multiple tables based on what you’re storing and a key to join it all back. That allows more flexibility for the target, and this is what I’ve been doing lately.

Here’s that query — I don’t have the INSERT statements from it, since I’m actually using the bcp method I mentioned earlier. Feel free to try this on a test server (remember, always on a test server):

/* SQL Server Agent Example
Finds information using system tables */

USE msdb
GO
SELECT jobs.name AS JobName
,steps.step_name AS StepName
, CONVERT(CHAR(10), CAST(STR(steps.run_date,8, 0) AS dateTIME), 111) AS DateRun
, STUFF(STUFF(RIGHT('000000' + CAST ( steps.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') AS TimeRun
, steps.run_duration AS StepDurationinSeconds
FROM sysjobhistory steps 
INNER JOIN sysjobs jobs
ON jobs.job_id = steps.job_id
ORDER BY steps.run_date, steps.run_time
GO

The ORDER BY isn’t strictly necessary, of course, since the storage order isn’t guaranteed anyway.

There are a few ways to find out how to join up the tables like I’ve done here. In some cases the join information is right inside Books Online — something you can look up easily to figure out the relationships with the system tables. Another method is to do a web search on the goal of your query or the actual table name itself. This can be problematic — you’re never sure of the skill level of the person you’re reading, or whether you’re looking at the right version and edition of the product. Over time these relationships might change (although that’s not very common) so you want to be careful with that approach.

I actually follow another approach — I open SQL Server Profiler and capture the statements run against my test server. Then I open SQL Server Management Studio and drill either into the Standard Reports or the Graphical Interface and look at the Transact-SQL (T-SQL) that it runs against the server.

Uptime — Using Windows Management Instrumentation (WMI) or Event Logs

Another method you can use to track longer-running activities is Windows Management Instrumentation or WMI calls. This has a few firewall, security and other ramifications, but it has the advantage of being able to get to just about anything running on a Windows box. It isn’t always easy to learn WMI queries, but as I mentioned earlier I’m using PowerShell (version 2.0) for these examples, which makes it pretty easy to use WMI.

I’m using the following query to get the uptime for the various SQL Server processes on my server. Keep in mind there are a few security implications for using WMI, which I’ll link to at the bottom of this tutorial. You can also use other tools for the WMI queries; I just find PowerShell to be more user-friendly:

forEach ($process in (Get-Process | where {$_.name -eq "sqlservr"})) {
$uptime=(get-date).Subtract($process.starttime);
write-Host $process.Path $uptime.Days $uptime.hours $uptime.minute $uptime.seconds
}

This is run on each server, and shows me the time the service has been up and running. For saving this data as a variable and then inserting that data into a SQL Server table, check out my tutorial series here on working with PowerShell and SQL Server.

Using the Logs from Windows

Another great source of information about processes on the system are the Windows Event Logs. There are several Event Logs, from the “System” log to the “application” log. There are also other logs that can be created, but these are the main ones to look at. In specific, SQL Server stores a great deal of information

$ServerStart =foreach ($InstanceStarted in (Get-EventLog -LogName application | Where-Object
 { $_.eventID -eq  17126})) {write-host $InstanceStarted.Source " started at " $InstanceStarted.TimeGenerated}
$ServerStop = foreach ($InstanceStopped in (Get-EventLog -LogName application | Where-Object
 { $_.eventID -eq  17148})) {write-host $InstanceStopped.Source " stopped at " $InstanceStopped.TimeGenerated}

There are some interesting things to note here – What I’ve done is to set a variable for the start times from the log I’m interested in called $ServerStart. I then loop through the “Application” Event log looking for event id 17126. This is a startup message for SQL Server. I write out the start time for that Instance. From there I perform the same process for event 17148 for the stop time. Then I can calculate the difference between the two.

Something to keep in mind is that the service might have actually failed with another error code, so this might not be the most trustworthy method. I actually don’t use this method anymore for uptime, but I do use it for other information that I want to track. I’m showing it here so you can see how to get the time variables.

Once again, I write this out and then put it up into SQL Server from bcp.

Using the SQL Server Error Log

The SQL Server “Error” logs are actually a text-based report on far more than just errors. It has some great information, including the start and stop times. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG, but you can set that, so it’s important to track where you installed it.

The same caveat for tracking start and stop times applies here — it’s possible to have a really bad failure and the log not be written to for the stop times. However, you’ll probably know pretty quickly if your server crashes — and this monitoring isn’t intended for that kind of thing anyway.

You can use multiple methods to read a text file — the important thing is that you’re looking for the start and stop times. They are just at the top of the file, and just there at the bottom. I actually use PowerShell (surprise surprise) and search for the text “all Rights” — as in “All Rights reserved”. It appears a second or two (close enough for what I need to know) after the server boots. You could also look for the master database to come online, a particular database and so on, all depending on what you want to know. With PowerShell this is a simple command, but you can use any number for tools to open, read and search for strings. Here’s what I do, again running from the directory where the log sites:

cat ERRORLOG | select-String –pattern “All Rights”

The first 22 characters are the date and time, and there you have it. You can use this method as I mentioned to search within the ErrorLog for other information that you would like to track.

Other Object to Track

It doesn’t stop there. Using the methods I’ve described in this short series you can gather information of lots of other longer-termed events in SQL Server, such as:

• Backups
• Transaction-log shipping
• Mirroring numbers
• Replication

Y mucho mas. I’ll leave it to you to try these simple tips and come up with your own methods.

InformIT Articles and Sample Chapters

I mentioned the Windows Management Interface in this update. You can get a wealth of information about this technology in Programming with Windows Management Instrumentation.

Books and eBooks

And there’s tons of info on WMI in the books Developing WMI Solutions: A Guide to Windows Management Instrumentation and Windows Management Instrumentation (WMI).

Online Resources

The official Microsoft documentation on WMI is here.