SQL Server Reference Guide

Building a Database Documenter, Part 2

Last updated Mar 28, 2003.

In part one of this set of articles, I explained that there are several programs you can buy to document and track your server's status and performance. In this article, I show you a few scripts to do the same thing.

This isn't a complete tutorial; in the future I'll show you how to build a database to hold the results of these scripts and the jobs to schedule them. This section should give you an idea of the kinds of information you can get — there's a lot more depth.

To get information about your server and its databases, we rely on three tools: the system tables, system stored procedures, and system functions.

The system tables are located in the master database. This database is the first one started by SQL Server, and it contains all the rest of the information SQL Server needs to start and secure the rest of them. Microsoft has stated that basing information on these tables is risky, as they do not guarantee the contents or structure of these tables during an upgrade or patch. You should never place anything in the master database; you could compromise your whole system if you do.

We also make use of one other database, msdb. This database is used for anything that is scheduled or controlled by the SQL Server Agent. This includes items like jobs, certain DTS packages, and any HTML output jobs.

I've broken these scripts up into parts for a couple of reasons. First, we can spend a little time explaining the methods for each one. Second, some information (such as the server name) is more static, and doesn't need to be gathered as often. Other information (like performance metrics) has a higher granularity and lends itself to more frequent collection. By breaking the script into smaller pieces, you can apply different schedules to the collection.

I normally save the results of these queries into a tracking database. By appending the name of the server to each line of data, I can "roll up" all the data on every server I have to monitor in one central place. I can then run metrics and reports to show performance trends, to project server and storage needs, and so forth.

So now: on with the scripts.

First, we'll get the basic information. We can get the server name (and the instance name) in several ways, but a simple system variable or two suffices:

SELECT @@SERVERNAME
SELECT @@SERVICENAME
GO

If you want to jump ahead a bit, you might need to convert the result if you're going to store it in a table. Here's how to do that:

SELECT CONVERT(VARCHAR(30),@@SERVERNAME)
GO

You can use that trick with any of the system variables I'll show you.

Now on to the version information. You can use another variable for that:

SELECT @@VERSION
GO

What you get back is the version of SQL Server you're running, along with a number. The number represents the service pack you have installed. Microsoft publishes the numbering in each service pack, but here's a quick table:

Version Number	Service Pack
8.00.194	Microsoft SQL Server 2000
8.00.384	Microsoft SQL Server 2000 SP1
8.00.532	Microsoft SQL Server 2000 SP2
8.00.760	Microsoft SQL Server 2000 SP3
8.00.818	Microsoft SQL Server 2000 SP3 w/ Cumulative Patch MS03-031
7.00.623	Microsoft SQL Server 7.0
7.00.699	Microsoft SQL Server 7.0 SP1
7.00.842	Microsoft SQL Server 7.0 SP2
7.00.961	Microsoft SQL Server 7.0 SP3
7.00.1063	Microsoft SQL Server 7.0 SP4
6.50.201	Microsoft SQL Server 6.5 RTM
6.50.213	Microsoft SQL Server 6.5 SP1
6.50.240	Microsoft SQL Server 6.5 SP2
6.50.258	Microsoft SQL Server 6.5 SP3
6.50.281	Microsoft SQL Server 6.5 SP4
6.50.415	Microsoft SQL Server 6.5 SP5
6.50.416	Microsoft SQL Server 6.5 SP5a
6.50.479	Microsoft SQL Server 6.5 SP5a Update

You can construct a rather fancy CASE statement to print out the versions based on the number.

Using the system variables gives you a lot of control over the format of the server, instance name and version, but there's a handy stored procedure that rolls a lot more information up into one set. Here's that stored procedure and the output it created on my system:

EXEC master..xp_msver
GO 
----------------------------------------------------------
Index Name        Internal_Value Character_Value                           
------ -------------------------------- -------------- ----------------------------------1  ProductName      NULL   Microsoft SQL Server
2  ProductVersion     524288   8.00.760
3  Language       1033   English (United States)
4  Platform       NULL   NT INTEL X86
5  Comments       NULL   NT INTEL X86
6  CompanyName      NULL   Microsoft Corporation
7  FileDescription     NULL   SQL Server Windows NT
8  FileVersion      NULL   2000.080.0760.00
9  InternalName      NULL   SQLSERVR
10  LegalCopyright     NULL   © 1988-2003 Microsoft Corp. All rights reserved.
11  LegalTrademarks     NULL   Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12  OriginalFilename     NULL   SQLSERVR.EXE
13  PrivateBuild      NULL   NULL
14  SpecialBuild      49807360  NULL
15  WindowsVersion     170393861  5.1 (2600)
16  ProcessorCount     1    1
17  ProcessorActiveMask    1    00000001
18  ProcessorType     586   PROCESSOR_INTEL_PENTIUM
19  PhysicalMemory     511   511 (536334336)
20  Product ID      NULL   NULL
(20 row(s) affected)

Knowing the version can be very useful if you're trying to diagnose a particular issue that was solved in a service pack.

Now that you know the a bit about the server, we need to make sure the maintenance plans and backups have been running on the databases. There are a few stored procedures and system variables we can look to here as well, and we can also make use of the msdb database. Here's the script for the backups:

SELECT   SUBSTRING(s.name,1,40)AS ’DatabaseName’,
  CAST(b.backup_start_date AS char(11))AS ’LastBackupDate ’,
  CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())
    THEN ’Backup is current within a day’
      WHEN b.backup_start_date > DATEADD(dd,-7,getdate())
    THEN ’Backup is current within one week’
    ELSE ’Backup is older than one week’
    END
    AS ’Status’

FROM   master..sysdatabases  s
LEFT OUTER JOIN  msdb..backupset b
  ON s.name = b.database_name
  AND b.backup_start_date = 
    (SELECT MAX(backup_start_date)
    FROM msdb..backupset
    WHERE database_name = b.database_name
    AND type = ’D’)    
WHERE    s.name <> ’tempdb’
ORDER BY   s.name
GO

Notice the CASE statements in this script, which check for the backups in the msdb database and set the status text. I've got a week set here, but you can adjust the granularity by editing the DATEADD lines.

Now you can get the maintenance plan histories:

SELECT   succeeded
  , plan_name
  , database_name
  , activity
  , end_time
  , duration
  , message
FROM msdb..sysdbmaintplan_history
ORDER BY 
  succeeded
GO

This brings back all steps in all plans, ordered by the success bit. You can limit the dates you bring back with the end_time column.

Now that you know whether the backups and maintenance are up to date, you can find out more about the databases. To find out about what databases are on the system, use this script:

EXEC sp_helpdb
GO

With that information, you can find out more about a particular database:

EXEC sp_helpdb ’pubs’ 
GO

Okay, now you've got a good start on our documentation.

There are lots more scripts you can run to find out everything that is going on with the server. Another useful stored procedure can tell you about the users on the system:

EXEC sp_helplogins
GO

Notice that most of these stored procedures started with sp_help. You can find more of these stored procedures in Books Online.

You may have noticed that the stored procedures are easier to use than going after the system variables and other scripts. So why not just use those system stored procedures?

You certainly can, especially if you're using a VB or C# program to present the results. However, if you intend to store the results in a database, you run into a problem. Several of these system stored procedures return more than one "set" of data. This means that a single statement can't fill a table, unless you do some fancy variable work.

So, what if you want the power of the system stored procedures but you want more control so that the return data can be placed in a table? You can use another stored procedure to find the SQL statements that SQL Server is running to produce this output, and then use those statements with the SELECT...INTO clause to fill out the tables in your monitoring database. Here's a sample:

EXEC sp_helptext ’master..sp_helpdb’
GO

If you execute that statement on your system, you'll see the inner workings of the Microsoft stored procedures.

There's a lot more to talk about here — we'll definitely return to the system tables and variables in future articles. The important thing is to build your own arsenal of scripts to document your systems.

Informit Articles and Sample Chapters

Hand-in-hand with database documentation is change control. You'll find a great article about that here.

Online Resources

The system properties I told you about are located here. This is a great resource with lots of information on servers and databases.