Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
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:
Microsoft SQL Server 2000
Microsoft SQL Server 2000 SP1
Microsoft SQL Server 2000 SP2
Microsoft SQL Server 2000 SP3
Microsoft SQL Server 2000 SP3 w/ Cumulative Patch MS03-031
Microsoft SQL Server 7.0
Microsoft SQL Server 7.0 SP1
Microsoft SQL Server 7.0 SP2
Microsoft SQL Server 7.0 SP3
Microsoft SQL Server 7.0 SP4
Microsoft SQL Server 6.5 RTM
Microsoft SQL Server 6.5 SP1
Microsoft SQL Server 6.5 SP2
Microsoft SQL Server 6.5 SP3
Microsoft SQL Server 6.5 SP4
Microsoft SQL Server 6.5 SP5
Microsoft SQL Server 6.5 SP5a
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.
The system properties I told you about are located here. This is a great resource with lots of information on servers and databases.