SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Overview VI - Monitoring Process Locks

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

Lock, unlock, lock, unlock is what databases do to prevent data from being updated from multiple sources at the same time. This of course can cause lock waits (blocks) for other SQL statements. This in turn causes applications to slow, jobs to run long, and eventually your pager to go off. To help you start working on the issue before your pager alerts you, you can use this new SSIS package to monitor for blocks across all your SQL Server 2000, 2005, and 2008 instances. In addition, it can check for some long running SQL Agent Jobs and verify SQL Agent is running.

This package is similar to the previous SQL Overview package, except it only retrieves data from the system table "dbo.sysprocesses" and is executed repeatedly throughout the day. Thus is the reason for it being a new package. You might have noticed the "dbo" system table is used instead of the "sys" system table. This was done to allow data to be collected from SQL Server 2000 instances.

Details on how to create this package is not covered by this article, instead it will cover how to use the package and its reports. If you wish to create one from scratch, please refer to the article SQL Overview Part 1 on how to get started. This article's file attachment contains the full package, reports, and setup instructions. The reports are created once a day with the other SQL Overview reports. Any issues detected during the previous 24 hours will be reported at that time.


The package is executed using SQL Server 2005. It is capable of accessing SQL Server 2000, 2005, and 2008 instances. The information it collects is stored in the new table dbo.SQL_Sysprocesses. Any errors detected while trying to connect are stored in the new table dbo.SSIS_Errors_Sysprocesses.

All servers in the table SSIS_ServerList are processed by this package unless its column Skip_SQL_Processes is set to True. The default is to check each server.

Rows in the table dbo.sysprocesses older than 7 days are deleted by this package. To change this setting, you need to update the package container "Prune SQL_Sysprocesses" with the desired value.

SQL used when collecting data from sysprocesses

DECLARE @@Package_Run_Date AS datetime
SET @@Package_Run_Date  GETDATE()

   @@SERVERNAME AS [Server],
   CASE WHEN program_name LIKE 'SQLAgent - TSQL%' 
        THEN (SELECT msdb.dbo.sysjobs.name
                FROM msdb.dbo.sysjobs
                WHERE program_name LIKE 'SQLAgent - TSQL%' 
                 AND SUBSTRING(program_name,  328)=
       (SUBSTRING(LEFT(msdb.dbo.sysjobs.job_id,8),7,2) +
       SUBSTRING(LEFT(msdb.dbo.sysjobs.job_id,8),5,2) +
       SUBSTRING(LEFT(msdb.dbo.sysjobs.job_id,8),3,2) +
        ELSE '' 
        END AS JobName,

   RTRIM([loginame]AS [loginame],
   RTRIM([lastwaittype]AS [lastwaittype],
   RTRIM([waitresource]AS [waitresource],
   DB_NAME (dbidAS DatabaseName,
   USER_NAME([uid]AS UserName,
        DATEDIFF(minute0GETDATE() - last_batch AS  DurationMinutes,
   GETDATE() - last_batch AS DurationDatetime,
   RTRIM([status]AS [status],
   RTRIM([hostname]AS [hostname],
   RTRIM([program_name]AS [program_name],
   RTRIM([nt_domain]AS [nt_domain],
   RTRIM([nt_username]AS [nt_username],
   @@Package_Run_Date  AS [Package_run_date]


New tables
  • dbo.SQL_Sysprocesses - Stores the results of querying dbo.sysprocess. See the SQL above for the columns that are captured.
  • dbo.SSIS_Errors_Sysprocesses - Stores errors encountered while executing the package.
  • rep.Sysprocesses_Blocked - Contains SQL processes reported as being blocked and the processes identified as the blocker.
  • rep.SQL_Overview_Last_Run_Date_Sysprocesses - Contains the last run date of the package. This date is used when checking for long running sysprocesses.

Existing Tables Used
  • dbo.SSIS_ServerList - Controls which instances are checked. Two new columns will be used: Skip_SQL_Processes and Skip_SQLAgent_Check.
  • rep.Long_Running_Jobs - Contain settings for checking for long running SQL Agent jobs. 


This package comes with four new reports that are executed once a day and sends an email whenever there is anything to report. Each report requires you to set the variable @EmailRecipients to the e-mail account of those who are to receive the report. If you specify multiple addresses, use a semicolon to separate them.

  • Report: SQL Server - SQL Overview - Sysprocesses Table Refresh Errors 
    Job: DBA-SQL Overview - Report Table Refresh Errors 

    Description: Report errors encountered during the previous 25 hours by the new sysprocesses package. After the report is sent, any errors older than 25 hours are deleted from the table SSIS_Errors_Sysprocesses.

  • Report: SQL Server - Blocked Sysprocesses Summary
    Job: DBA-SQL Overview - Report Blocked Sysprocesses 

    Description:  Report processes blocked during the previous 24 hours that had a wait time of 1 minute or more. Both the process being blocked and the process causing the block are included in the report.

  • Report: SQL Server - Long Running Executing Jobs (Sysprocesses)
    Job: DBA-SQL Overview - Report Long Running Executing Jobs-Sysprocesses 

    Description: Report any running SQL Agent job that is running longer than expected. The threshold used to identify the job is stored in the table rep.Long_Running_Jobs. A system default value is also stored in this table. Once a job is reported as running long, a row is added to this table allowing you to customize its setting. Maintenance Plan jobs are not reported because they do not include the Job Id in its program_name. For me it listed "Microsoft SQL Server Management Studio" instead of the Job Id.

  • Report: SQL Server - SQL Agent Stopped on Server
    Job: DBA-SQL Overview - Report SQLAgent Stopped

    Description: This report checks the captured sysprocesses tables for a SQL Agent processes. For servers missing this process, a report is generated. This test is not 100% accurate. So far, it has been right more times than wrong. The time when it has been wrong is when instance connections are failing. Right or wrong, this extra check cannot hurt. However, it has helped in determining why a server is missing all its last nights' backups.

    To skip any instances for this reports, set the column Skip_SQLAgent_Check to True in the table SSIS_ServerList. The default is to check.


  • To execute the reports after each time the package has finished, add start report job steps to the "DBA-SQL Overview - Sysprocesses".
  • To collect information about SQL Agent jobs and sysprocesses near the same time, add a start Job for "DBA-SQL Overview - Sysprocesses" in the job "SQL Overview" and disable the schedule in the sysprocesses job. See SQL Overview V for this package.
  • To adjust the time when you will receive an alert long run time, change the settings in rep.Long_Running_Jobs.
  • To skip an instance when collecting sysprocesses date, change its Skip_SQL_Processes setting in SSIS_ServerList  to be skipped.
  • To skip a instance when checking if SQL Agent is running, change its Skip_SQLAgent_Check setting in SSIS_ServerList to be skipped
  • To change the number of days rows are kept in dbo.sysprocesses, update the package container "Prune SQL_Sysprocesses" with the desired value. 


Now you will know when locks are causing issues and can take the appropriate action. The reports are created once a day but can be modified to run more often. All the code is included in the attachments and you are welcome to customize it. 

The run time for this package is quick. For me it runs 2.5 minutes and collects the sysprocesses table data from 30+ instances. It will run longer when an instance is down because it has to wait to timeout before connecting to the next server.

Additional Resources



Total article views: 15450 | Views in the last 30 days: 0
Related Articles

SQL Overview SSIS Package III - Full Package

Continuing on with his series on reporting for your SQL Server, David Bird brings together the serie...





SQL Overview VII - SSRS Reports and Subscriptions

In the next installment of his series on the SQL Overview toolkit, David Bird looks at the informati...


Overview of Published Articles – 2015Q4

Here is an overview of the articles I published in the final quarter of 2015: Integrated Logging wi...


Overview of DTS Packages

While SQL Server 2005 is greatly changing the paradigm of ETL with Integration Services, SQL Server ...