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:
[IMAGE]
(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:
[IM
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
AGE]
(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:
[IMAGE]
(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:
master.dbo.xp_sqlagent_enum_jobs
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, 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:
[IMAGE]
(Click on code for script download.)
Step 3:
The central stored procedure should look like this:
[IMAGE]
(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:
[IMAGE]
(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.
[TABLE]
