Problem solve Get help with specific problems with your technologies, process and projects.

Stored procedure to monitor long-running jobs in SQL Server 2000

Monitoring long-running jobs in SQL Server 2000 is not a simple practice, but it is a best practice among DBAs. In this tip, try out a stored procedure that easily identifies long-running jobs. You'll also receive email alerts about these jobs that poorly affect SQL Server performance.

The problem:

Monitoring long-running jobs in SQL Server 2000 is a complicated task. The problem is that the job execution history is written only after the first job step is completed. Therefore, if the first -- or only -- step of the job runs for a long time, you can't query any system table or function in order to capture its start time.

In this article, you'll learn about a T-SQL fix that retrieves information about long-running jobs and overcomes this problem.

Quick fix to identify long-running SQL Server jobs:

One option to easily solve this problem is to add a dummy first step to the job. The dummy step should do a simple and quick task, such as SELECT GETDATE(). Upon adding this step, the sysjobhistory table is monitored while the next steps of the job are executing. The run_date and run_time fields of this first step in the sysjobhistory table will show the date and time when the step completed and, thus, it will show the date and time when the next steps started.

Here is an example that shows the list of running jobs where the second step had started more than two minutes ago:

script showing the list of running jobs where the second step had started more than two minutes ago
(Click on code for script download.)

What about long-running SQL Server jobs in a complex environment?

What if you have hundreds of SQL jobs in dozens of servers? What if you are not entitled to add steps to jobs -- for example, when the jobs were built by an external vendor?

Adding another step to all the existing jobs may become extremely time-consuming or not allowed.

Let's talk about an alternate way to monitor long-running SQL jobs.

My goal here is to check the long-running jobs from a centralized location and gather information about them into a central table. Note: A long-running SQL Server 2000 job in my environment is one running for more than 4 to 4.5 hours.

In SQL Server 2000 Enterprise Manager, the job status is shown in the list of jobs:

View of job status in SQL Server 2000 Enterprise Manager
(Click on image for enlarged view.)

If you run SQL Profiler to monitor what is running when the Job list pane is opened, here's what you'll get:

exec msdb..sp_help_job

Since I'd like to store the results of the jobs in a central table, I cannot use the sp_help_job stored procedure.

If you run the command, INSERT… EXEC msdb..sp_help_job, in order to save the output in a table, it will fail because the stored procedure sp_help_job calls inner stored procedures.

Here's the solution:

Step 1:
I already have a central database named DBADB that was created for a similar reason -- gathering information from remote servers for management purposes.

The DBADB database contains a table with the list of all the "active" Linked Servers. I also created a Link Server for each of the servers on the list.

The table of servers (tbl_ServerList) contains only one field named ServerName.

So, in order to gather information from all the Linked Servers, I need to loop over the list of servers in the table and execute whatever's necessary.

For the long-running jobs information, I create a central table:

script creating a central table
(Click on code for script download.)

Step 2:
Looking carefully at the source code of the sp_help_job stored procedure, it appears that the following extended stored procedure runs and queries job information directly from the SQL Server Agent:


Since I don't want to rely on having MSDTC (Microsoft Distributed Transaction Coordinator), and since I will use Linked Servers to query the information from the remote servers,

More on Performance Tuning SQL Server:

 I will have to create a stored procedure on every REMOTE server participating in this process. The creation of this SP should be done once on each remote server and prior to the startup of the central process described in the following step. This stored procedure will run the master.dbo.xp_sqlagent_enum_jobs SP and insert the results into a temporary table. That way, my central SP can read the data from the temporary table using Linked Server.

I have a DBADB database on each server, so I can create this stored procedure there. You could create it in the master database, although it's not recommended. The stored procedure should look like this:

stored procedure created in the DBADB database
(Click on code for script download.)

Step 3:
The central stored procedure should look like this:

central stored procedure
(Click on code for script download.)

Step 4:
Execute or schedule the execution of the SP as needed. For example, I scheduled it to run every 30 minutes because I need to be notified immediately when a job is running for more than 4 to 4.5 hours. The next step in my job looks for long-running jobs and send me an email alert with the list, if there are such jobs.

The query to select long-running SQL jobs should look similar to the following:

query to select long-running SQL jobs
(Click on code for script download.)

Monitoring your SQL Server environment to identify long-running jobs is a best practice for DBAs. It gives you an idea about performance degradation on the different jobs and also alerts you about jobs that might affect performance of other activities in your SQL Server environment. If you thought this was an impossible mission with SQL Server 2000, just follow the steps in this article, copy the stored procedures and you're ready to go.

Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T-SQL/packages coding, and more.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning