Skip to main content
added script to get blocking process callstack from blocked-process-report
Source Link

This is a typical callstack from blocking process\executionStack nodes of blocked_process_reportblocked-process-report\blocking-process\process\executionStack in my case:

      <executionStack>
        <frame line="30" stmtstart="1570" stmtend="1796" sqlhandle="0x0300ff7f66f82acdcaae2d0054a9000001000000000000000000000000000000000000000000000000000000" />
        <frame line="917" stmtstart="67266" stmtend="67522" sqlhandle="0x0300ff7f48dddfc2a8b02d0054a9000001000000000000000000000000000000000000000000000000000000" />
        <frame line="52" stmtstart="3496" stmtend="5162" sqlhandle="0x0300ff7f5eea5ef7d25b2d0054a9000001000000000000000000000000000000000000000000000000000000" />
        <frame line="92" stmtstart="4236" stmtend="4768" sqlhandle="0x0300ff7f6366c5c416b12d0054a9000001000000000000000000000000000000000000000000000000000000" />
      </executionStack>

If I decode it with this query:

select
cast(
             (select SUBSTRING(txt.text,(ISNULL(T.x.value('./@stmtstart', 'int'), 0) / 2) + 1,
                           ((CASE ISNULL(T.x.value('./@stmtend', 'int'), -1)
                                  WHEN -1 THEN DATALENGTH(txt.text)
                                  ELSE T.x.value('./@stmtend', 'int')
                              END - ISNULL(T.x.value('./@stmtstart', 'int'), 0)) / 2) + 1) + CHAR(13) AS statement_txt
                              ,T.x.value('./@line', 'int') as line
                       from @bpReportXml.nodes('//blocking-process/process/executionStack/frame') AS T(x)
                       cross apply sys.dm_exec_sql_text(T.x.value('xs:hexBinary(substring((./@sqlhandle), 3))', 'varbinary(max)')) AS txt
                       for XML path('')) as xml) AS frame_blocking_process_xml

Then I get this result:

This is a typical callstack from blocking process\executionStack nodes of blocked_process_report in my case:

This is a typical callstack from blocked-process-report\blocking-process\process\executionStack in my case:

      <executionStack>
        <frame line="30" stmtstart="1570" stmtend="1796" sqlhandle="0x0300ff7f66f82acdcaae2d0054a9000001000000000000000000000000000000000000000000000000000000" />
        <frame line="917" stmtstart="67266" stmtend="67522" sqlhandle="0x0300ff7f48dddfc2a8b02d0054a9000001000000000000000000000000000000000000000000000000000000" />
        <frame line="52" stmtstart="3496" stmtend="5162" sqlhandle="0x0300ff7f5eea5ef7d25b2d0054a9000001000000000000000000000000000000000000000000000000000000" />
        <frame line="92" stmtstart="4236" stmtend="4768" sqlhandle="0x0300ff7f6366c5c416b12d0054a9000001000000000000000000000000000000000000000000000000000000" />
      </executionStack>

If I decode it with this query:

select
cast(
             (select SUBSTRING(txt.text,(ISNULL(T.x.value('./@stmtstart', 'int'), 0) / 2) + 1,
                           ((CASE ISNULL(T.x.value('./@stmtend', 'int'), -1)
                                  WHEN -1 THEN DATALENGTH(txt.text)
                                  ELSE T.x.value('./@stmtend', 'int')
                              END - ISNULL(T.x.value('./@stmtstart', 'int'), 0)) / 2) + 1) + CHAR(13) AS statement_txt
                              ,T.x.value('./@line', 'int') as line
                       from @bpReportXml.nodes('//blocking-process/process/executionStack/frame') AS T(x)
                       cross apply sys.dm_exec_sql_text(T.x.value('xs:hexBinary(substring((./@sqlhandle), 3))', 'varbinary(max)')) AS txt
                       for XML path('')) as xml) AS frame_blocking_process_xml

Then I get this result:

added 1899 characters in body
Source Link

This is a typical callstack from blocking process\executionStack nodes of blocked_process_report in my case:

<statement_txt>EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0)
        
</statement_txt>
<line>30</line>
<statement_txt>exec sys.sp_MSsetfilteredstatus @tabid

                    -- clear nonsqlsub status for this article.

                    
</statement_txt>
<line>917</line>
<statement_txt>EXEC @retcode = @cmd
                    @publication,
                    @article,
                    @subscriber,
                    @status,
                    @previous_status,
                    @destination_db,
                    @frequency_type,
                    @frequency_interval,
                    @frequency_relative_interval,
                    @frequency_recurrence_factor,
                    @frequency_subday,
                    @frequency_subday_interval,
                    @active_start_time_of_day,
                    @active_end_time_of_day,
                    @active_start_date,
                    @active_end_date,
                    @optional_command_line,
                    @distribution_jobid OUTPUT,
                    @from_auto_sync,
                    @ignore_distributor,
                    -- Agent offload
                    @offloadagent,
                    @offloadserver,
                    @dts_package_name,
                    @dts_package_password,
                    @dts_package_location,
                    @skipobjectactivation,
                    @distribution_job_name,
                    @publisher,
                    @publisher_type
                    ,@ignore_distributor_failure

    
</statement_txt>
<line>52</line>
<statement_txt>EXECUTE @retcode =   sys.sp_changesubstatus 
                        @publication = @publication,
                        @article = @article,
                        @status = @status,
                        @from_auto_sync = @from_auto_sync,
                        @skipobjectactivation = @skipobjectactivation,
                        @publisher = @publisher

    
</statement_txt>
<line>92</line>

This is a typical callstack from blocking process\executionStack nodes of blocked_process_report in my case:

<statement_txt>EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0)
        
</statement_txt>
<line>30</line>
<statement_txt>exec sys.sp_MSsetfilteredstatus @tabid

                    -- clear nonsqlsub status for this article.

                    
</statement_txt>
<line>917</line>
<statement_txt>EXEC @retcode = @cmd
                    @publication,
                    @article,
                    @subscriber,
                    @status,
                    @previous_status,
                    @destination_db,
                    @frequency_type,
                    @frequency_interval,
                    @frequency_relative_interval,
                    @frequency_recurrence_factor,
                    @frequency_subday,
                    @frequency_subday_interval,
                    @active_start_time_of_day,
                    @active_end_time_of_day,
                    @active_start_date,
                    @active_end_date,
                    @optional_command_line,
                    @distribution_jobid OUTPUT,
                    @from_auto_sync,
                    @ignore_distributor,
                    -- Agent offload
                    @offloadagent,
                    @offloadserver,
                    @dts_package_name,
                    @dts_package_password,
                    @dts_package_location,
                    @skipobjectactivation,
                    @distribution_job_name,
                    @publisher,
                    @publisher_type
                    ,@ignore_distributor_failure

    
</statement_txt>
<line>52</line>
<statement_txt>EXECUTE @retcode =   sys.sp_changesubstatus 
                        @publication = @publication,
                        @article = @article,
                        @status = @status,
                        @from_auto_sync = @from_auto_sync,
                        @skipobjectactivation = @skipobjectactivation,
                        @publisher = @publisher

    
</statement_txt>
<line>92</line>
Source Link

I faced the same issue and I saw a lot of locks generated by same statements and deadlocks happened on server during the same 3 minutes.

Via deadlock reports I figured out that deadlocks happened with my processes and this system process in my case: Proc [Database Id = 32767 Object Id = -993696157]

select DB_NAME(32767)--NULL
select OBJECT_NAME(-993696157)--sp_MSactivate_auto_sub

According to this article it is related to replication system task.