Return to Revisions

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.

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>