Why does a create trigger (that already exists) block all processes in SQL Server 2016 SP3?

Question

I have a situation where the following DDL Statement is rising to the top of my list of processes which I have ordered by the number of processes that each process is blocking (directly or indirectly). Killing this process frees up everything. Before you kill it almost everything backs up behind it (almost, but not quite).

CREATE TRIGGER [dbo].[MailReturnCBRHandling] ON dbo.ACC 
AFTER UPDATE
AS

Declare 
@v_Agency_ID            varchar(15),
@n_SaleID           numeric,
@n_DebtorID         numeric,
@n_AccNo            numeric,
@v_AccStatID            varchar(20),
@b_AccStatMailReturned  bit,
@b_AccStatReportsToCBR  bit,
@b_DebtorMailReturned       bit,
@b_AccReportsToCBR      bit

IF UPDATE(Stat_ID)

BEGIN

DECLARE c_InsertedMailReturnCBR CURSOR FOR
    SELECT Agency_ID, SaleID , Debtor_ID,  AccNo , Stat_ID,  IsNull(IsBureau,0)
    FROM INSERTED

OPEN c_InsertedMailReturnCBR
FETCH NEXT FROM c_InsertedMailReturnCBR INTO @v_Agency_ID, @n_SaleID, @n_DebtorID, @n_AccNo, @v_AccStatID, @b_AccReportsToCBR 

WHILE @@FETCH_STATUS = 0
BEGIN
    -- *************************************************    Get AccStat MailReturned & ReportsToCBR      *************************************************
    DECLARE c_AccStat CURSOR FOR
        SELECT MailReturned ,  ReportsToCBR FROM AccStat WHERE Agency_ID = @v_Agency_ID AND AccStat = @v_AccStatID
    
    OPEN c_AccStat
    FETCH NEXT FROM c_AccStat INTO @b_AccStatMailReturned, @b_AccStatReportsToCBR
    
    -- *************************************************    Get Debtor MailReturned    *************************************************
    DECLARE c_Debtor CURSOR FOR 
        SELECT IsNull(MailReturned,0) FROM Debtor WHERE Agency_ID = @v_Agency_ID AND Debtor_ID =  @n_DebtorID

    OPEN c_Debtor
    FETCH NEXT FROM c_Debtor INTO @b_DebtorMailReturned
            
    -- *************************************************    UPDATE MailReturned     *************************************************

    IF  @b_AccStatMailReturned IS NOT NULL
    BEGIN
        IF @b_AccStatMailReturned = 1 AND @b_DebtorMailReturned = 0 
        BEGIN
            UPDATE Debtor SET MailReturned = 1 WHERE Debtor_ID =  @n_DebtorID
            INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress) 
            VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Debtor Mail Returned changed from: False To: True','C', GetDate() ,0)
        END
    
        IF @b_AccStatMailReturned = 0 AND @b_DebtorMailReturned = 1
        BEGIN
            UPDATE Debtor SET MailReturned = 0 WHERE Debtor_ID =  @n_DebtorID
            INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress) 
            VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Debtor Mail Returned changed from: True To: False','C', GetDate() ,0)
        END
    END
    -- *************************************************    UPDATE ReportsToCBR   *************************************************

    IF @b_AccStatReportsToCBR IS NOT NULL 
    BEGIN
        IF @b_AccStatReportsToCBR = 1 AND @b_AccReportsToCBR = 0 
        BEGIN
            UPDATE ACC SET IsBureau = 1  WHERE AccNo =  @n_AccNo
            INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress,AccNo,SaleID) 
            VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Reports To Bureau changed from: False To: True','C', GetDate() , 0, @n_AccNo, @n_SaleID)
        END
    
        IF @b_AccStatReportsToCBR = 0 AND @b_AccReportsToCBR = 1
        BEGIN
            UPDATE ACC SET IsBureau = 0  WHERE AccNo =  @n_AccNo
            INSERT INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress,AccNo,SaleID) 
            VALUES (@v_Agency_ID, @n_DebtorID, 'Auto Generated Note --> Reports To Bureau changed from: True To: False','C', GetDate() , 0, @n_AccNo, @n_SaleID)
        END
    END
    FETCH NEXT FROM c_InsertedMailReturnCBR INTO @v_Agency_ID, @n_SaleID, @n_DebtorID, @n_AccNo, @v_AccStatID, @b_AccReportsToCBR 
    CLOSE c_Debtor
    DEALLOCATE c_Debtor 
    CLOSE c_AccStat
    DEALLOCATE c_AccStat
END
CLOSE c_InsertedMailReturnCBR
DEALLOCATE c_InsertedMailReturnCBR
END

The trigger is written by the company that provides us with the software that we use to run out business. There is no need to create it because it already exists. It should instantly fail because an object of that name already exists.

The vendor provided this software runs the entire application logged in as SA. I have begged my management to make them run all sql under the windows credential of the logged in user but they claim that they have no way to force the software vendor to to that so the SQL process list does not tell me anything about who submitted the create trigger and under what circumstances. We are running SQL Server 2016 SP3.

I am wondering:

1. Why would this suspend? Why not just return an error tha t says an object of that name already exists?

2. Given that it has suspended, why do all the other tasks back up behind it? Put another way, what resource is this process holding that the others need.

I can't believe that they are all waiting to create or alter triggers or that something that this process needs to change is in a page that everyone else would want. It would be as if this process was able to put a lock on the entire database, but why?

This happens about every two weeks. Exact same trigger. I have not yet determined a set of circumstances that trigger it. Thank you.

Answer 1

As mentioned by others, this is not the CREATE running, it's just the trace showing the full batch text. You can get the actual statement currently running using the following snippet (where st is dm_exec_sql_text and qs is dm_exec_query_stats or dm_exec_requests or similar).

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
        ((CASE qs.statement_end_offset  
          WHEN -1 THEN DATALENGTH(st.text)  
         ELSE qs.statement_end_offset  
         END - qs.statement_start_offset)/2) + 1)

---

I'll be honest, your real issue here is most likely the trigger code itself, which uses not one, not two but three cursors. And it's all unnecessary. Burn this trigger with fire, nuke from orbit, it's a total shambles.

You can do this with just two joined UPDATEs, using OUTPUT to get the logging notes. It's also missing a recursion check, as one of the updates is on ACC itself (why that is is unclear).

CREATE TRIGGER [dbo].[MailReturnCBRHandling] ON dbo.ACC 
AFTER UPDATE
AS

SET NOCOUNT ON;

IF NOT UPDATE(Stat_ID)  -- column was present in the UPDATE
  OR TRIGGER_NESTLEVEL(@@PROCID, 'AFTER', 'DML') > 1   -- this trigger is already called
  OR NOT EXISTS (SELECT 1 FROM inserted)  -- no rows
    RETURN;


UPDATE d
SET MailReturned = ast.MailReturned
  OUTPUT i.Agency_ID, d.Debtor_ID, 'Auto Generated Note --> Debtor Mail Returned changed from: ' + IIF(inserted.MailReturned = 0, 'True To: False', 'False To: True'), 'C', GetDate(), 0
  INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress)
FROM inserted i
JOIN AccStat ast ON ast.Agency_ID = i.Agency_ID AND ast.AccStat   = i.Stat_ID
JOIN Debtor d  ON d.Agency_ID = i.Agency_ID AND d.Debtor_ID = i.Debtor_ID
WHERE ISNULL(d.MailReturned, 0) <> ast.MailReturned;


UPDATE acc
SET IsBureau = ast.ReportsToCBR
  OUTPUT i.Agency_ID, d.Debtor_ID, 'Auto Generated Note --> Reports To Bureau changed from: ' + IIF(inserted.IsBureau = 0, 'True To: False', 'False To: True'), 'C', GetDate(), 0, i.AccNo, i.SaleID
  INTO DebtorNote (AGENCY_ID, DEBTOR_ID, NOTE, Priority, StampTime, Supress, AccNo, SaleID)
FROM inserted i
JOIN AccStat ast ON ast.Agency_ID = i.Agency_ID AND ast.AccStat = i.Stat_ID
JOIN ACC acc ON acc.AccNo = i.AccNo
WHERE ISNULL(i.IsBureau, 0) <> ast.ReportsToCBR;

This should substantially speed up your trigger, and therefore hopefully make it much less likely for you to get big blocking issues.

Answer 2

Why would this suspend? Why not just return an error tha t says an object of that name already exists?

It is not actually trying to run the CREATE TRIGGER statement. The text being returned is from a session that is actually firing the trigger, but it is presented in sys.dm_exec_sql_text as the whole "CREATE" statement.

See this answer for details on using the statement offset to fetch the actual portion of the trigger being executed that is causing the issue: https://dba.stackexchange.com/a/62856/136353

Given that it has suspended, why do all the other tasks back up behind it? Put another way, what resource is this process holding that the others need.

You need to get the lock information to see what locks are being held by the blocking session and what locks are requested by the blocked sessions to understand why this is causing the issue.

I like to use Adam Machanic's sp_WhoIsActive for this as a lot of the hard work has been done for you (helpful links: One, Two).

My guess is the trigger firing session is holding locks on one or more of the Debtor, DebtorNote, Acc or AccStat table(s) and your other sessions are requesting locks on those tables, hence the blocking.