Check SQL Server's Status

Use Outlook and a 2-way pager to keep in touch

[Editor's Note:Share your SQL Server discoveries, comments, problems, and solutions with other readers. Email your Reader to Reader contributions (400 words or fewer) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $100.]

As a Microsoft lead test engineer working on one of the largest data warehouses ever to run on SQL Server, I need to be able to check the exact status of source file transmission into the data warehouse at any time from anywhere.

This need became painfully apparent 4 months into our development of Microsoft's World Wide Marketing Database (WWMDB). Our team was nearing a project milestone—the initial load of the database—and had to ensure system reliability and data quality for all data-source feeds involved. With only a short time to meet our deadline for this milestone, I thought our only option was to maintain 24-hour shifts of team members submitting source files, monitoring progress, and debugging any problems. But I found a better way for us to stay in touch.

I had already been using my alphanumeric pager to receive urgent emails while I was away from my workstation. By applying the pager-alert concept to the team's current problem, I devised a solution that meets our needs and that can work for any developer or DBA who needs to be able to retrieve the status of any SQL Server object, on demand, at any time and from any location.

The solution works with SQL Server 2000, 7.0, or 6.5 and requires Microsoft Outlook 2000 and a two-way pager. You must also be running SQL Mail on the SQL Server you want to monitor. Under SQL Server 7.0, you can see whether SQL Mail is running or stopped by opening SQL Server Enterprise Manager and selecting a server listed under SQL Server Group. Under the server item, expand the database folder items, then select the Support Services folder, which will show SQL Mail's status.

Here's how the solution works. You send an email message from a pager to your work email address. Outlook analyzes the email based on rules you've set up and calls a .cmd file that calls a stored procedure on SQL Server to retrieve the desired status results. SQL Server then uses SQL Mail to email the results back to your pager.

The first step is to define which statuses you want to see so that you can code them into your stored procedures. Useful statuses might be file size, database size, Data Transformation Services (DTS) or replication confirmation, and availability or usage statistics. You're then ready to code the stored procedures that return the statuses.

Listing 1 shows the Check Transmission Status stored procedure our test team uses, and Figure 1, page 18, shows the result set SQL Server returns. (The online listings for this article also include a stored procedure to check database file size.) Notice that the stored procedure converts values to a fixed length so that results are legible on your pager. After you've coded all the necessary procedures and created them on all the SQL Server systems you want to monitor, you need to create a .cmd file for each stored procedure. Simply designate a reliable file share, then create .cmd files containing a line of code to call the stored procedures:

osql /Sjuicy /dmaster /E /Q"exec sp_filemetrics"

With your stored procedures in place, you're ready to define Outlook rules that will call the .cmd files. With your Inbox highlighted, go to Tools, Rules Wizard, then New. You want to select the new rule Check messages when they arrive, as Screen 1 shows. Screen 2 shows conditions under which you want Outlook to apply the new rule: where my name is in the To box and with specific words in the body. Outlook will use the words you designate to call the appropriate .cmd file. To enter specific words for Outlook to search for, click the text specific words in the Rule description box.

The Rules Wizard next asks What do you want to do with the message? As shown in Screen 3, Select the start application check box, then click the text application in the Rule description box to specify that you want to launch the .cmd file associated with the words you told Outlook to search for in the email message.

Repeat these rule-defining steps for each stored procedure and .cmd file you write. When you're finished, run each .cmd file at least once to make sure it works correctly with its corresponding stored procedure. Then, with the command prompt box still displayed after executing the .cmd files, set the box to close on exit, which allows for seamless execution when you call these files remotely. If you don't set the box to close on exit, you'll have a command box instance for every .cmd file Outlook calls.

One last reminder: Make sure you code your stored procedures to return the result sets to your pager email address. To test the setup, send an email to your work account with one of your specified strings in the body of the message. Your Outlook rule will recognize the string and call the proper .cmd file, which will call the proper stored procedure. The stored procedure will then retrieve the designated result set and email it to your pager.

I use a Motorola PageWriter 2000X, which lets me send and receive email messages and pages from just about anywhere in the service range. PageWriter costs slightly more than a regular alphanumeric pager, but you can easily realize a return on your investment when you have these stored procedures and Outlook rules in place.