SQL Overview V - Monitoring Long Running Jobs

An unexpected long running job cause delays for users, developers, and

DBAs all trying to get their work done. Sometimes they are symptoms of

problems on a server that need to be addressed before bigger issues

arise. Being notified when a job runs long provides a heads up of a

potential problem. The SQL Overview SSIS package published in the

previous articles is capable of reporting long running SQL Agent jobs

once they have finished. However, it does not check for any actively

running jobs that are taking longer than expected.

This article adds a container to this SSIS package that

captures running SQL Agent jobs for SQL Server 2005 and 2008 instances.

It uses the new stored procedure msdb.dbo.sp_help_jobactivity added in SQL Server 2005. Although I could use the table msdb.dbo.sysjobactivity,

I chose to use the new stored procedure because it provides

more information that I want to capture. Since the stored

procedure was released with 2005, it can be executed on 2005 or later.

In addition to this new container, several new reports are

being added. They report on long running finished jobs and jobs

currently running longer than expected. Run times used to check these

jobs are saved in a new table. This table contains a system

default and an optional custom setting for each job. The reports will

be generated once a day along with the rest of the SQL Overview

reports. These reports can be created repeatedly throughout the day

using a separate package (see attachment) that just collects

information from msdb.dbo.sp_help_jobactivity.

Due to its length, this article has been split into sections;

Package Update, Reports, and Tips. If you rather not update an old SSIS

package, a new version is attached to this article with the scripts for

all reports and table definitions. This complete SSIS package was

developed in SQL Server 2005 and has numerous fixes. In addition, it

has been updated to execute in case sensitive SQL Server

instances.

Package Update

Create New Tables

You must create all new tables before updating the SSIS package with the new container.

Create table dbo.Job_Activity

This table stores the output from the system stored procedure msdb.dbo.sp_help_jobactivity.

USE SQL_Overview
GO
CREATE TABLE [dbo].[Job_Activity](
 [Server] [nvarchar](128) NOT NULL,
 [session_id] [int] NOT NULL,
 [job_id] [uniqueidentifier] NOT NULL,
 [job_name] [sysname] NOT NULL,
 [run_requested_date] [datetime] NULL,
 [run_requested_source] [sysname] NULL,
 [queued_date] [datetime] NULL,
 [start_execution_date] [datetime] NULL,
 [last_executed_step_id] [int] NULL,
 [last_exectued_step_date] [datetime] NULL,
 [stop_execution_date] [datetime] NULL,
 [next_scheduled_run_date] [datetime] NULL,
 [job_history_id] [int] NULL,
 [message] [nvarchar](1024) NULL,
 [run_status] [int] NULL,
 [operator_id_emailed] [int] NULL,
 [operator_id_netsent] [int] NULL,
 [operator_id_paged] [int] NULL,
 [Row_inserted_date] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Job_Activity] 
 ADD CONSTRAINT [DF_Job_Activity_Server] DEFAULT (@@servername) 
 FOR [Server]
GO
ALTER TABLE [dbo].[Job_Activity] 
 ADD CONSTRAINT [DF_Job_Activity_Package_run_date] DEFAULT (GETDATE()) 
 FOR [Row_inserted_date]
GO

Create report table rep.Long_Running_Jobs

This table contains the long running job settings and any jobs

reported as running long. Each job can have a custom long runtime

setting in the column Alert_RunTime_Minutes. The row with the server

name of "DEFAULT" is used as the default for all jobs that do have a

specific entry. If there is an entry but the "alert setting" is NULL,

this row is also used. Do not worry if the default row is missing, a

new one is created with a default value of 120 minutes.

USE SQL_Overview
GO
CREATE TABLE [rep].[Long_Running_Jobs](
 [Server] [nvarchar](128) NOT NULL,
 [Job_Name] [nvarchar](128) NOT NULL,
 [Alert_RunTime_Minutes] [bigint] NULL,
 [Last_Reported_Job_Duration] [nvarchar](50) NULL,
 [Last_Reported_Date] [datetime] NOT NULL,
 CONSTRAINT [PK_Long_Running_Jobs_Thresholds] PRIMARY KEY CLUSTERED 
(
 [Server] ASC,
 [Job_Name] ASC
)
) ON [PRIMARY]
GO
ALTER TABLE [rep].[Long_Running_Jobs] 
 ADD CONSTRAINT [DF_Long_Running_Jobs_Last_Report_date] DEFAULT (GETDATE()) 
 FOR [Last_Reported_Date]
GO

Create TEMPDB table tempdb.dbo.Job_Activity

This table is used to store the data from the stored procedure msdb.dbo.sp_help_jobactivity and pass it back to the permanent table in the SQL_Overview database.

USE tempdb
GO
CREATE TABLE [tempdb].[dbo].[Job_Activity](
   [Server] [nvarchar](128) NOT NULL CONSTRAINT [DF_Job_Activity_Server] DEFAULT (@@servername),
       [session_id] [int] NOT NULL,
        [job_id] [uniqueidentifier] NOT NULL,
       [job_name] [sysname] NOT NULL,
      [run_requested_date] [datetime] NULL,
       [run_requested_source] [sysname] NULL,
      [queued_date] [datetime] NULL,
      [start_execution_date] [datetime] NULL,
     [last_executed_step_id] [int] NULL,
 [last_executed_step_date] [datetime] NULL,
  [stop_execution_date] [datetime] NULL,
      [next_scheduled_run_date] [datetime] NULL,
  [job_history_id] [int] NULL,
        [message] [nvarchar](1024) NULL,
    [run_status] [int] NULL,
    [operator_id_emailed] [int] NULL,
   [operator_id_netsent] [int] NULL,
   [operator_id_paged] [int] NULL,
     [Row_inserted_date] [datetime] NOT NULL CONSTRAINT [DF_Row_inserted_date] DEFAULT (getdate())
) ON [PRIMARY]
GO

Note: This TEMPDB table along with all other tables must exist on the local instance before you can start editing the SSIS package.

Update SSIS Package

Using SQL Server Business Intelligence Development Studio, edit SQL_Overview_Package.dtsx.

Truncate Table Job_Activity

Add "Execute SQL Task" object to the Truncate Tables container
Settings - double click on icon
- Name: Truncate Job Activity
- Connection: QASRV.SQL_Overview
- SQL Statement: TRUNCATE Table Job_Activity
- BypassPrepare: False

Collect Job Activity

Add "Foreach Loop Container" between " Collect Backup History" and "Collect Job History"
1. Connect the " Collect Backup History" task to this item with the
  green line/arrow and remove the connection to "Collect Job History"
2. Settings
  1. General
    1. Name: Collect Job Activity
  2. Collection
    1. Change Enumerator to Foreach ADO enumerator
    2. Select ADO object source variable User::SQL_RS
  3. Variable Mapping
    1. Add User::SRV_Conn
  4. Click OK
3. Right click on this container
4. Select Properties
5. Set MaximumErrorCount to 999

Add "Execute SQL Task" to the "Foreach Loop Container"

Double click icon > Settings: General

Name: Get Job Activity
Connection Type: OLE DB
Connection: MultiServer
SQLSourceType: Direct input

SQLStatement:

USE [tempdb]
GO
IF EXISTS 
 (SELECT * 
 FROM tempdb.dbo.sysobjects 
 WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Job_Activity]')
 ) 
 DROP TABLE [tempdb].[dbo].[Job_Activity]
GO

CREATE TABLE [tempdb].[dbo].[Job_Activity](
 [Server] [nvarchar](128) NOT NULL CONSTRAINT [DF_Job_Activity_Server] DEFAULT (@@servername),
 [session_id] [int] NOT NULL,
 [job_id] [uniqueidentifier] NOT NULL,
 [job_name] [sysname] NOT NULL,
 [run_requested_date] [datetime] NULL,
 [run_requested_source] [sysname] NULL,
 [queued_date] [datetime] NULL,
 [start_execution_date] [datetime] NULL,
 [last_executed_step_id] [int] NULL,
 [last_exectued_step_date] [datetime] NULL,
 [stop_execution_date] [datetime] NULL,
 [next_scheduled_run_date] [datetime] NULL,
 [job_history_id] [int] NULL,
 [message] [nvarchar](1024) NULL,
 [run_status] [int] NULL,
 [operator_id_emailed] [int] NULL,
 [operator_id_netsent] [int] NULL,
 [operator_id_paged] [int] NULL,
 [Row_inserted_date] [datetime] NOT NULL CONSTRAINT [DF_Row_inserted_date] DEFAULT (getdate())
) ON [PRIMARY]
GO


If Object_Id('[msdb].[dbo].[sp_help_jobactivity]') is Not Null
BEGIN

DELETE FROM [tempdb].[dbo].[Job_Activity]

INSERT INTO [tempdb].[dbo].[Job_Activity]
 ([session_id]
 ,[job_id]
 ,[job_name]
 ,[run_requested_date]
 ,[run_requested_source]
 ,[queued_date]
 ,[start_execution_date]
 ,[last_executed_step_id]
 ,[last_exectued_step_date]
 ,[stop_execution_date]
 ,[next_scheduled_run_date]
 ,[job_history_id]
 ,[message]
 ,[run_status]
 ,[operator_id_emailed]
 ,[operator_id_netsent]
 ,[operator_id_paged]
)
 EXECUTE [msdb].[dbo].[sp_help_jobactivity] 

END

Click OK

Add "Data Flow Task" to the "Foreach Loop Container"
1. Connect the green arrow from "Get Job Activity" to it
2. Rename to Load Job Activity
3. Right click on this task
4. Select Properties
5. Set MaximumErrorCount to 999
Select the Data Flow Tab or double click on icon for "Data Flow task"

Add "OLE DB Source" from toolbox. This is used to read the TEMPDB table on the remote instance.

Double click icon

OLE DB Connection manager: MultiServer
Change Data access mode to SQL Command

SQL Command Text:

SELECT [Server]
 ,[session_id]
 ,[job_id]
 ,[job_name]
 ,[run_requested_date]
 ,[run_requested_source]
 ,[queued_date]
 ,[start_execution_date]
 ,[last_executed_step_id]
 ,[last_exectued_step_date]
 ,[stop_execution_date]
 ,[next_scheduled_run_date]
 ,[job_history_id]
 ,[message]
 ,[run_status]
 ,[operator_id_emailed]
 ,[operator_id_netsent]
 ,[operator_id_paged]
 ,[Row_inserted_date]
 FROM [tempdb].[dbo].[Job_Activity]

Click Preview to verify the SQL and then click Close when done
Click OK

Add "OLE DB Destination" from toolbox. This is used to store the data in the local instance.
1. Connect the "OLE DB Source" to this element with the green line/arrow
2. Double click on the Icon for OLE DB Destination and make the following changes
  1. OLE Connection Manager: QASRV.SQL_Overview
  2. Name of the table or the view: [dbo].[Job_Activity]
  3. Click Mappings and confirm that the columns mappings are correct.
  4. Click OK
Connect a green line from "Collect Job Activity" to "Collect Job History".
Remove the green line directly connecting containers "Collect Backup History" and Collect Job History"

If this is way too confusing, try using the complete package attached to this article.

Reports

To create the new reports, use the attached file e-NewReports.sql and change the variable "??@domain.com" to your e-mail address before executing the SQL.

DBA-SQL Overview - Report Long Running Finished Jobs.

This report lists all finished jobs that

ran longer than expected since the previous execution of the SQL

Overview package. To remove a job from this report, update its setting

in the table rep.Long_Running_Jobs.

This job adds a row to the table rep.Long_Running_Jobs

for each reported long running job. It also updates its last

job run duration to help you in setting a more appropriate value. If

the overall default alert setting is missing, it will also be added.

DBA-SQL Overview - Report Long Running Executing Jobs (JobActivity)

This report lists all actively running

jobs that are taking longer than expected to finish. To remove a job

from this report, update its setting in the table rep.Long_Running_Jobs.

SQL Overview Job Update

Update the SQL Overview Job to create the new TEMPDB table on the

host server and execute the new reports. The job will fail if the

required tables do not exist on the local server.

Add the TEMPDB table tempdb.dbo.Job_Activity definition to step one of the SQL Overview Job. Use the SQL provided earlier in this article.

Add Start Job steps for the two new reports.

EXEC msdb.dbo.sp_start_job N'DBA-SQL Overview - Report Long Running Finished Jobs'

EXEC msdb.dbo.sp_start_job N'DBA-SQL Overview - Report Long Running Executing Jobs-JobActivity'

Finished

You have completed all the required changes and now you can test

your package and its reports. All the SQL used is available within

this article's attachment.

Tips

Keep your reports small and pertinent by making use of the custom job settings in the table rep.Long_Running_Jobs.
For help in identifying job contention, use the SQL_Overview sql below to see when jobs are running at the same time.
USE SQL_Overview GO
SELECT [Server],[name], [run_date], [run_time],
[run_duration], [Hours], [Minutes], [Seconds],[step_id]
FROM Job_History a
WHERE [step_id] = 0
ORDER BY [Server], [run_date] DESC, [run_time] DESC, [run_duration] DESC
For extra help in reviewing job contention, try the free tools; SQLjobvis from SQLSoft or SQL Job Manager from Idera.
Blocked waits also cause jobs to run long.
This SSIS package was developed using SQL Server 2005 and can
connect to SQL Server 2008 instances. However, if you want to host it
on a SQL Server 2008 instance it will need to be converted.
Originally, I ran the long running active job report once a day.
While troubleshooting, I created a new package to allow the report
to be run throughout the day. It has been useful and has been included
it in the attachments.

Conclusion

This article's file attachment includes all the SQL used in this

article, a SQL Agent only package, and the full package installation

with all the reports. The next article will contain a

new package used to collect information on sysprocesses to help

identify blocks and other issues.

SQL Overview V - Monitoring Long Running Jobs

Package Update

Create New Tables

Update SSIS Package

Reports

SQL Overview Job Update

Finished

Tips

Conclusion

Additional Resources

Resources

Rate

Share

Categories

Share

Rate

SQL Overview V - Monitoring Long Running Jobs

Package Update

Create New Tables

Update SSIS Package

Reports

SQL Overview Job Update

Finished

Tips

Conclusion

Additional Resources

Resources

Rate

Share

Categories

Share

Rate

Related content

Checking the Job Status on Multiple Servers

"sysperfinfo" in SQL Server

Basics of C2 Auditing

Monitoring Drive and Database Free Space

Automatically Gathering Server Information Part 1