none
How to get an email when my blocked process times out telling me what was blocking... RRS feed

  • Question

  • We have a C# process, Partner Synch, that sends some SQL to a SQL 2005 DB that times out about every three days or so. We know the SQL that times out, the victim, of the .NET command timeout. It is attempting to update one table and seems to be blocked by another process. We’d like to set up a trace of some sort that tells us as much as possible about what process is doing the blocking at the time of the timeout. Ideally, we’d like to know the SQL that is blocking our Partner Synch process, because then we could determine the C# or sproc responsible.

     

    The blocking process ID is not too useful because we’d have trouble knowing what it corresponded to. Ideally, we’d like to see something like:

     

    On October 25th at 10:45AM, your process, procX, whose SQL, ‘Update tblA set col1 = 0 where ID=2’ was blocked and terminated by its .NET client after the 600 second .NET timeout was reached. When the timeout occurred, procZ’s SQL ‘update tblA set col2=1’ was blocking.

    I realize this is pretty difficult because it involves not only SQL but also .NET, but you have to admit, it would be a nice tool to have. Has anybody seen or developed any tool like this?

    Thanks in advance,

    Jeff Roughgarden, MCSD, MCDBA

     

    Thursday, October 21, 2010 10:56 PM

Answers

  • In SQL Server 2005, there is an event Blocked Process Report that can help you in capturing information that you are looking for and then you an customize report using available information. Here is link that explains this in detail http://msdn.microsoft.com/en-us/library/ms191168.aspx

    You may also try using SQL Server Process Monitor tool available on codeplex http://sqlblockedprocessmon.codeplex.com/

     


    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.
    Friday, October 22, 2010 4:50 AM
  • Use WMI class and you will be notified through mail once you have any blocking ....please read the script carefully ....run it on 2005 SP2 (SP1 has bug)

     

    /*******************************************************************************************
    *  This script will create an Alert to Monitor Blocking event.
    *  The alert will run a job and the job will enter data in a table.
    *  For any suggestion contact :abhay_c@hotmail.com
    *  Please adjust Blocked Process Threshold in sp_configure first
    *  Please check the Token replacement option under agent properties >> alert system
    *******************************************************************************************/

    /* Step 1: creating the table to capture the Event information */

    USE Master
    GO

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BLOCKED_PROCESS_REPORT]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[BLOCKED_PROCESS_REPORT]
    GO

    CREATE TABLE [dbo].[BLOCKED_PROCESS_REPORT] (
    [PostTime] [datetime] NOT NULL ,
    [SQLInstance] varchar(20),
    [Databaseid] int,
    [computerName] Varchar(20),
    [SessionLoginName] Varchar(30),
    [SPID] int,
    [TransactionID] int,
    [EventSequence] int,
    [objectID] int,
    [IndexID] int,
    [TextData] nvarchar(4000) ,
    [duration] int,
    [RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
    [Flag] [int] NOT NULL CONSTRAINT [DF_BLOCKED_PROCESS_REPORT_Flag]  DEFAULT ((0))
    ) ON [PRIMARY]
    GO

    CREATE INDEX [BLOCKED_PROCESS_REPORT_IDX01] ON [dbo].[BLOCKED_PROCESS_REPORT]([Posttime]) WITH FILLFACTOR = 100 ON [PRIMARY]
    GO

    /*Step 2 : Creating the Job that will enter values into the Deadlockevents table created above*/
    /*Service account and sql operator option are optional*/

    USE [msdb]
    GO

    IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture BLOCKED_PROCESS_REPORT Event')
    EXEC msdb.dbo.sp_delete_job @job_name = N'Capture BLOCKED_PROCESS_REPORT Event', @delete_unused_schedule=1

    GO
    --DECLARE @ServiceAccount varchar(128)
    --SET @ServiceAccount = N'<job_owner_account>'
    --DECLARE @SQLOperator varchar(128)
    --SET @SQLOperator = N'<sql_agent_operator>'

    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    END

    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture BLOCKED_PROCESS_REPORT Event',
    @enabled=1,
    @notify_level_eventlog=2,
    @notify_level_email=3,
    @notify_level_netsend=0,
    @notify_level_page=0,
    @delete_level=0,
    @description=N'Job for responding to blocking events',
    @category_name=N'[Uncategorized (Local)]',
    --@owner_login_name=@ServiceAccount,
    --@notify_email_operator_name=@SQLOperator,
    @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /*Step 3: Insert graph into LogEvents*/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents',
    @step_id=1,
    @cmdexec_success_code=0,
    @on_success_action=1,
    @on_success_step_id=0,
    @on_fail_action=2,
    @on_fail_step_id=0,
    @retry_attempts=0,
    @retry_interval=0,
    @os_run_priority=0, @subsystem=N'TSQL',
    @command=N'

    INSERT INTO BLOCKED_PROCESS_REPORT (
    [PostTime] ,
    [SQLInstance] ,
    [Databaseid] ,
    [computerName],
    [SessionLoginName],
    [SPID] ,
    [TransactionID] ,
    [EventSequence] ,
    [objectID] ,
    [IndexID] ,
    [TextData],
    [duration]
    )

    VALUES (
    GETDATE(),
    N''$(ESCAPE_NONE(WMI(SQLInstance)))'',
    N''$(ESCAPE_NONE(WMI(Databaseid)))'',
    N''$(ESCAPE_NONE(WMI(ComputerName)))'',
    N''$(ESCAPE_NONE(WMI(SessionLoginname)))'',
    N''$(ESCAPE_NONE(WMI(SPID)))'',
    N''$(ESCAPE_NONE(WMI(TransactionID)))'',
    N''$(ESCAPE_NONE(WMI(EventSequence)))'',
    N''$(ESCAPE_NONE(WMI(objectid)))'',
    N''$(ESCAPE_NONE(WMI(indexid)))'',
    N''$(ESCAPE_SQUOTE(WMI(Textdata)))'',
    N''$(ESCAPE_NONE(WMI(Duration)))''
    )',
    @database_name=N'master',
    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    GO

    /*Creating the alert and associating it with the Job to be fired */

    USE [msdb]
    GO

    IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to BLOCKED_PROCESS_REPORT')
    EXEC msdb.dbo.sp_delete_alert @name=N'Respond to BLOCKED_PROCESS_REPORT'

    GO

    DECLARE @server_namespace varchar(255)
    IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
    SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
    ELSE
    SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

    EXEC msdb.dbo.sp_add_alert @name=N'Respond to BLOCKED_PROCESS_REPORT',
    @enabled=1,
    @notification_message=N'Your Message',
    @wmi_namespace=@server_namespace,
    @wmi_query=N'SELECT * FROM BLOCKED_PROCESS_REPORT Where Duration > 30',
    @job_name='Capture BLOCKED_PROCESS_REPORT Event' ;

    GO


    --select * from master..BLOCKED_PROCESS_REPORT


     
    --go--/* Step 5: Create a stored proc for sending the [Errorlog] information as .CSV file */
    use master
    go
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[blocking_rpt]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP proc [dbo].[blocking_rpt]
    go
    Create proc [dbo].[blocking_rpt]
    as
    DECLARE @SQL varchar(2000)
    DECLARE @date nvarchar (2000)
    DECLARE @File nvarchar(1000)
    select @date= GETDATE()
    SET @SQL = 'select textdata from [master..BLOCKED_PROCESS_REPORT] where flag=0'
    SET @File = '[Blocking]report_'+@date+'.csv'

    EXECUTE msdb.dbo.sp_send_dbmail
    @profile_name = 'xxxx',
    @recipients = 'xxxxx@xxxx.com',
    @subject = 'Blocking report',
    @body = '***URGENT***Attached please find the [Blocking] report',
    @query =@SQL ,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = @file,
    @query_result_header = 1,
    @query_result_separator = ' ',
    @query_result_no_padding = 1,
    @query_result_width = 32767

    go
    /* Step 6: Changing the flag to 1 so that next time this information is not sent*/

    use master
    go
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.change_flag_blocking') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP proc dbo.change_flag_blocking
    go
    create proc [dbo].[change_flag_blocking] as
    update master..BLOCKED_PROCESS_REPORT set flag = 1 where flag = 0
    go

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Friday, October 22, 2010 5:27 AM

All replies

  • In SQL Server 2005, there is an event Blocked Process Report that can help you in capturing information that you are looking for and then you an customize report using available information. Here is link that explains this in detail http://msdn.microsoft.com/en-us/library/ms191168.aspx

    You may also try using SQL Server Process Monitor tool available on codeplex http://sqlblockedprocessmon.codeplex.com/

     


    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.
    Friday, October 22, 2010 4:50 AM
  • Use WMI class and you will be notified through mail once you have any blocking ....please read the script carefully ....run it on 2005 SP2 (SP1 has bug)

     

    /*******************************************************************************************
    *  This script will create an Alert to Monitor Blocking event.
    *  The alert will run a job and the job will enter data in a table.
    *  For any suggestion contact :abhay_c@hotmail.com
    *  Please adjust Blocked Process Threshold in sp_configure first
    *  Please check the Token replacement option under agent properties >> alert system
    *******************************************************************************************/

    /* Step 1: creating the table to capture the Event information */

    USE Master
    GO

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[BLOCKED_PROCESS_REPORT]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[BLOCKED_PROCESS_REPORT]
    GO

    CREATE TABLE [dbo].[BLOCKED_PROCESS_REPORT] (
    [PostTime] [datetime] NOT NULL ,
    [SQLInstance] varchar(20),
    [Databaseid] int,
    [computerName] Varchar(20),
    [SessionLoginName] Varchar(30),
    [SPID] int,
    [TransactionID] int,
    [EventSequence] int,
    [objectID] int,
    [IndexID] int,
    [TextData] nvarchar(4000) ,
    [duration] int,
    [RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
    [Flag] [int] NOT NULL CONSTRAINT [DF_BLOCKED_PROCESS_REPORT_Flag]  DEFAULT ((0))
    ) ON [PRIMARY]
    GO

    CREATE INDEX [BLOCKED_PROCESS_REPORT_IDX01] ON [dbo].[BLOCKED_PROCESS_REPORT]([Posttime]) WITH FILLFACTOR = 100 ON [PRIMARY]
    GO

    /*Step 2 : Creating the Job that will enter values into the Deadlockevents table created above*/
    /*Service account and sql operator option are optional*/

    USE [msdb]
    GO

    IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture BLOCKED_PROCESS_REPORT Event')
    EXEC msdb.dbo.sp_delete_job @job_name = N'Capture BLOCKED_PROCESS_REPORT Event', @delete_unused_schedule=1

    GO
    --DECLARE @ServiceAccount varchar(128)
    --SET @ServiceAccount = N'<job_owner_account>'
    --DECLARE @SQLOperator varchar(128)
    --SET @SQLOperator = N'<sql_agent_operator>'

    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    END

    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture BLOCKED_PROCESS_REPORT Event',
    @enabled=1,
    @notify_level_eventlog=2,
    @notify_level_email=3,
    @notify_level_netsend=0,
    @notify_level_page=0,
    @delete_level=0,
    @description=N'Job for responding to blocking events',
    @category_name=N'[Uncategorized (Local)]',
    --@owner_login_name=@ServiceAccount,
    --@notify_email_operator_name=@SQLOperator,
    @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /*Step 3: Insert graph into LogEvents*/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents',
    @step_id=1,
    @cmdexec_success_code=0,
    @on_success_action=1,
    @on_success_step_id=0,
    @on_fail_action=2,
    @on_fail_step_id=0,
    @retry_attempts=0,
    @retry_interval=0,
    @os_run_priority=0, @subsystem=N'TSQL',
    @command=N'

    INSERT INTO BLOCKED_PROCESS_REPORT (
    [PostTime] ,
    [SQLInstance] ,
    [Databaseid] ,
    [computerName],
    [SessionLoginName],
    [SPID] ,
    [TransactionID] ,
    [EventSequence] ,
    [objectID] ,
    [IndexID] ,
    [TextData],
    [duration]
    )

    VALUES (
    GETDATE(),
    N''$(ESCAPE_NONE(WMI(SQLInstance)))'',
    N''$(ESCAPE_NONE(WMI(Databaseid)))'',
    N''$(ESCAPE_NONE(WMI(ComputerName)))'',
    N''$(ESCAPE_NONE(WMI(SessionLoginname)))'',
    N''$(ESCAPE_NONE(WMI(SPID)))'',
    N''$(ESCAPE_NONE(WMI(TransactionID)))'',
    N''$(ESCAPE_NONE(WMI(EventSequence)))'',
    N''$(ESCAPE_NONE(WMI(objectid)))'',
    N''$(ESCAPE_NONE(WMI(indexid)))'',
    N''$(ESCAPE_SQUOTE(WMI(Textdata)))'',
    N''$(ESCAPE_NONE(WMI(Duration)))''
    )',
    @database_name=N'master',
    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:
    GO

    /*Creating the alert and associating it with the Job to be fired */

    USE [msdb]
    GO

    IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to BLOCKED_PROCESS_REPORT')
    EXEC msdb.dbo.sp_delete_alert @name=N'Respond to BLOCKED_PROCESS_REPORT'

    GO

    DECLARE @server_namespace varchar(255)
    IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
    SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
    ELSE
    SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

    EXEC msdb.dbo.sp_add_alert @name=N'Respond to BLOCKED_PROCESS_REPORT',
    @enabled=1,
    @notification_message=N'Your Message',
    @wmi_namespace=@server_namespace,
    @wmi_query=N'SELECT * FROM BLOCKED_PROCESS_REPORT Where Duration > 30',
    @job_name='Capture BLOCKED_PROCESS_REPORT Event' ;

    GO


    --select * from master..BLOCKED_PROCESS_REPORT


     
    --go--/* Step 5: Create a stored proc for sending the [Errorlog] information as .CSV file */
    use master
    go
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[blocking_rpt]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP proc [dbo].[blocking_rpt]
    go
    Create proc [dbo].[blocking_rpt]
    as
    DECLARE @SQL varchar(2000)
    DECLARE @date nvarchar (2000)
    DECLARE @File nvarchar(1000)
    select @date= GETDATE()
    SET @SQL = 'select textdata from [master..BLOCKED_PROCESS_REPORT] where flag=0'
    SET @File = '[Blocking]report_'+@date+'.csv'

    EXECUTE msdb.dbo.sp_send_dbmail
    @profile_name = 'xxxx',
    @recipients = 'xxxxx@xxxx.com',
    @subject = 'Blocking report',
    @body = '***URGENT***Attached please find the [Blocking] report',
    @query =@SQL ,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = @file,
    @query_result_header = 1,
    @query_result_separator = ' ',
    @query_result_no_padding = 1,
    @query_result_width = 32767

    go
    /* Step 6: Changing the flag to 1 so that next time this information is not sent*/

    use master
    go
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.change_flag_blocking') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP proc dbo.change_flag_blocking
    go
    create proc [dbo].[change_flag_blocking] as
    update master..BLOCKED_PROCESS_REPORT set flag = 1 where flag = 0
    go

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Friday, October 22, 2010 5:27 AM
  • Thank you for responding, Mohan and Abhay. I will try the script that Abhay kindly included in his response. I already have a job that uses the service broker to email me when deadlocks occur. This seems similar. I will let you know how it all goes.
    Monday, October 25, 2010 4:18 PM