SQL Server Reference Guide

In an earlier tutorial I explained a bit about database maintenance, and in another tutorial I showed you how to use a SQL Server Wizard to automate maintenance steps into a full maintenance plan, using the graphical tools in SQL Server 2000 or 2005.

A maintenance plan, as I explained, is really just a set of SQL Server Agent Jobs in SQL Server 2000, and in 2005 you can use the newer SQL Server Integration Services (SSIS) packages with specific operations and flow to create the maintenance. The Maintenance Wizard has three ways to notify you that the jobs have completed and whether they were successful. Of course, with either version you can edit the jobs or steps that you create with the wizards.

The first method is the e-mail notification that the SQL Server Agent mail (or Database Mail in SQL Server 2005) sends. This method is great, because you don’t have to do anything except read the e-mail messages. The downside is that you get one e-mail for each Job in SQL Server 2000. If you have several plans (each of which each have many jobs), that can add up to a lot of e-mail. You have to sort through each message to make sure all the steps finished correctly. In my company, we have multiple SQL Servers, some with dozens of plans, and dozens of Jobs. One person simply can’t wade through that much mail, so of course it just doesn’t get done.

Another gotcha with the e-mail method is that the server needs to be able to send you mail. That’s fine for most production servers, but how about testing systems? You don’t want to pollute your production mail system with test accounts, so those servers aren’t good candidates to get checked with the e-mail method. As most developers will tell you, testing servers need maintenance too.

Another disadvantage is that you have to appoint someone who really knows what to look for to check these e-mails. What the server sends is a technical glut of tons of information, even though the only bit you generally care about is whether the job was successful. If it isn’t, that’s when the rest of the information becomes useful.

The second method of Maintenance Plan completion checks involves log files. Again, this is a per-step set of logs with quite a bit of information to sort through to find out whether the step completed successfully. This method has all the disadvantages of the e-mail route, with the added problem of needing to be accessible; you have to log on to the server directly or share out the log location, presenting a security risk. Both SQL Server Management Studio (SQL Server 2005) and Enterprise Manager (SQL Server 2000) contain a log file viewer, and both versions use text files for those logs so that you can read them with other tools as well.

The third way that you can check the Maintenance Plans uses graphical tools as well. In the graphic below, I’ve opened Enterprise Manager and navigated to the Management object and then the Maintenance Plan item.

Figure 25

Right-clicking that item brings up the menu item of Maintenance Plan History.

Figure 26

Once I select that item, you can see the panel shown below:

Figure 27

You can see the various steps and information about them in a grid. My screen resolution forces me to scroll to the right to see the columns that show whether the plan was successful, but you’ll notice that several filters can be applied. I can look at the steps that failed, or the steps that succeeded. While this is useful, to truly be safe I need to see both. I might see a few steps that were successful and think everything is OK when it’s not; or I might see nothing that failed when in fact nothing ran.

In addition to those limitations, the data shows all dates — more information than I want to see at one time. I only care about a day or two at a time.

So what I need is a way to see only a certain number of days, and only the columns of data I really care about, from a central location, that just about anyone can check. What I’ll show you in this tutorial kills two birds with one stone. I’ll explain how to solve this problem, and how to use another wizard along the way. This tool is called the Web Assistant, and it creates web pages using a Stored Procedure. In SQL Server 2000, you can use a Wizard (a graphical series of panels that asks you questions) to call the Stored Procedure, as I'll describe below. In SQL Server 2005, this feature is being replaced, and I'll explain more about that in a moment.

In the panel shown here, I’ve clicked the Tools menu item in Enterprise Manager:

Figure 28

Next, I click Wizards to bring up the screen shown below.

Figure 29

This panel shows the various wizards that SQL Server provides. Notice that I’ve expanded the Management object. The wizard we’re interested in is the Web Assistant Wizard. Clicking that item brings up the following panel:

Figure 30

This panel explains that the wizard will do three things. It will set the source for the data, set the schedule, and set the format. I click Next, and we see the following screen:

Figure 31

It’s pretty obvious what’s happening here, but it’s important to note that I can get data from more than one database at a time — I’ll show you how in a moment. For the purposes of this tutorial, I only need data from the msdb database, but if you are going to pull the data from more than one database, just put one of them here.

Once I’ve selected the database, I click Next to see the screen shown in the following graphic:

Figure 32

Here I’m doing two things — I’m naming the job that the wizard will create, and I’m setting the source for the data.

As you can see, the first option is to select columns from tables. This is the simplest source of data, and I’ve not found it to be particularly useful. The exception to that is when another job populates a small table for this output.

The next choice is to have the results of a stored procedure form the source of the data. I like this option since it provides the most flexibility. A stored procedure can display data but can also affect data, with inserts, updates, deletes and so forth. This option along with the next one will allow you to select data from more than one database, by the way. Just structure your statement to fully qualify the server, owner, object name. A stored procedure has the added advantage of running on the SQL Server, so it is an efficient use of resources.

The option I’ve chosen is to enter a SQL statement directly into the wizard. I’ve done this to show you the statement I’ve created, and to encapsulate the tutorial.

I click Next to bring up the following panel:

Figure 33

In this screen I’ve entered a slightly modified form of the SQL Query I showed you for the maintenance plan history output from my last tutorial. Just so you can copy it easily, here it is in text:

USE msdb
GO
SELECT ’Succeeded: ’ + CAST(succeeded AS VARCHAR(1))
,’Completed on: ’ + CAST(end_time AS VARCHAR(11)) 
,’Database Name: ’ + database_name 
,’Activity: ’ + activity 
,’Duration: ’ + CAST(duration as varchar(1)) 
,message 
FROM sysdbmaintplan_history 
WHERE DATEDIFF(day, end_time, getdate()) < 1 
ORDER BY succeeded, database_name

You can see that I’m using the table that the maintenance plan maintains, and I’m using a query to limit the amount of data it returns. The ORDER BY qualifier places the failed steps at the top — those are the ones I really care about.

Notice in the line directly above the ORDER BY statement I’ve limited the output to the jobs that occurred today. If your plan is arranged differently, then you may need to adjust the number at the end of that statement to include more days. Making this adjustment will also show you more days if you’re not checking daily (shame on you!).

Here’s a tip from experience-land: Enter the query in Query Analyzer first, make sure you get the right results, then copy and paste it in here. While the color-syntax checking works, there’s no preview of the data before you commit the statement, and it’s a bit difficult to edit the statement later.

Once I’m satisfied with the query, I click Next to bring up the screen shown here:

Figure 34

There are a lot of options about the schedule on this panel. The first is just once, right after the wizard runs. This option is fine if you just want one Web page.

The second option is on-demand, meaning that the job will be created, and must be called manually when you want the page created. You normally do this as part of another job or perhaps with a trigger with the sp_runwebtask command.

The third option is to create the page once, but later. Again, not super-useful, at least in this case.

The fourth option is to create the Web page whenever the data changes. Unless you’re making that table I spoke about earlier, to hold the results of another process, I highly recommend you not use this option. While it may seem to make sense, I’ve seen a server buried trying to create the pages when a data change is made 100 times a minute. Just say no.

The final option is the one I use the most. With this choice you set the wizard to use a recurring schedule.

Notice I’ve also selected the option to go ahead and have a page created when I’m done. That’s to show you the output without having to get up at 6:00am in the morning. No, I wouldn’t really do that.

After I make my choice I click Next to bring up this screen:

Figure 35

Here I’m setting the schedule, which is set to every morning at 6:00. You can get pretty frequent with this, so again you’ll want to be careful here. Don’t bury your server with this kind of work.

After I set this option I select Next to bring up this panel:

Figure 36

This sets the location of the completed page. I have IIS installed on my SQL Server, so I’ve set the page to go to a root location so that it can be checked.

Once I set the location, I click Next to bring up this panel:

Figure 37

Here I’m given two choices: to allow SQL to help me format the page, or to select a template. I’ll walk you through the SQL assistance. To use a template you set up any normal HTML code to format the page, but for the actual data you set up a place for the data. If you format your page as you wish, then you only have to use the line shown below to accept the output of the data:

<%insert_data_here%>

Of course there’s more to it than that. If you want more formatting of the data, see the references section of the InformIT SQL Server Web site.

For now, let’s continue on and let SQL Server format the page. I click next, and we see the panel shown below:

Figure 38

Here you can see various options for setting the page headings, table titles and data and time stamps. I like the date and time stamp so that I know the wizard ran, just another way to make sure everything is working.

Once I make those selections, I click Next to bring up the screen shown below:

Figure 39

This panel continues the process of setting up the page format. I’ve made my changes and selected Next to bring up this screen:

Figure 40

This is a great option. It allows you to set no hyperlinks, one hyperlink (as I’ve done here), or even a set of hyperlinks, all drawn from a table. That table can be set by yet another application, making this a very extensible process.

After I make my changes, I click Next and we see the following panel:

Figure 41

Here I’m given the opportunity to parse the data out per page. I can also limit the results at this point. I’m going to leave the defaults here and just click Next. That brings up this screen:

Figure 42

On this panel, the wizard summarizes the actions it will take, and also gives me the opportunity to write the actions out to a SQL script file. The advantage of that is that I can use the script as backup or to port to another server.

Finally, I click the Finish button. Voila! The page is complete. To show you the output, take a look at this Web site:

Figure 43

I see that several steps ran successfully on my server. It’s still important I have a count of how many steps should run during the day. I need to count and make sure that works, or perhaps my query could also return that number and the count of the rows I return. Otherwise, I might gain a false sense of security, believing that I’m in good shape since no 0’s were returned.

This process does a few things for me. I limit the data to just what I care about, I can train someone else to look at the page without the other party having a great deal of knowledge, and I have a central location available to check the plans.

As I mentioned earlier, SQL Server 2005 does not include this Wizard. You should use the built-in Reporting Services tool to create a far more robust report where you can include graphs and so on. However, the stored procedures used to make the pages are still here. You do have to enable them first with these commands:

sp_configure ’show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ’Web Assistant Procedures’, 1;
GO
RECONFIGURE
GO

Now you can use the following Stored Procedures to create the web page:

InformIT Articles and Sample Chapters

Are you new to web pages in general? We have a great starting point for you here.

Online Resources

There's another article on the stored procedures here.