Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Overview V - Monitoring Long Running Jobs

By David Bird, (first published: 2010/03/24)

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
  1. 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
  2. Add "Execute SQL Task" to the "Foreach Loop Container"
    1. Double click icon > Settings: General
      1. Name: Get Job Activity
      2. Connection Type: OLE DB
      3. Connection: MultiServer
      4. SQLSourceType: Direct input
      5. 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
      6. Click OK
  3. 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
  4. Select the Data Flow Tab or double click on icon for "Data Flow task"
  5. Add "OLE DB Source" from toolbox. This is used to read the TEMPDB table on the remote instance. 
    1. Double click icon
      1. OLE DB Connection manager: MultiServer
      2. Change Data access mode to SQL Command
      3. 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]
      4. Click Preview to verify the SQL and then click Close when done
      5. Click OK
  6. 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
  7. Connect a green line from "Collect Job Activity"  to "Collect Job History".
  8. 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

  1. Keep your reports small and pertinent by making use of the custom job settings in the table rep.Long_Running_Jobs.

  2. For help in identifying job contention, use the SQL_Overview sql below to see when jobs are running at the same time.

  3. 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
  4. For extra help in reviewing job contention, try the free tools; SQLjobvis from SQLSoft or SQL Job Manager from Idera.

  5. Blocked waits also cause jobs to run long. 

  6. 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. 

  7. 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.

Additional Resources


Resources:

SQL_Overview5.zip
Total article views: 16610 | Views in the last 30 days: 23
 
Related Articles
ARTICLE

Enterprise SQL Job Activity Reporting

A SSIS/SSRS solution that provides SQL job activity for all your SQL Servers.

FORUM

executing sql server 2005 reports

executing sql server 2005

FORUM

Job Activity Monitor Report?

Automated Job Activity Monitor Report?

FORUM

SQL Server 2005 on active active cluster.

SQL Server 2005 on active active cluster.

FORUM

SQL Server 2005 Activity Monitor

SQL Server 2005 Activity Monitor

Tags
jobs    
monitoring    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones