Blog Post

Are ABORT_AFTER_WAIT's victims logged?

,

In SQL Server, using the KILL command to terminate a session results in an entry being logged in the error log.

This raises the question: Does the ALTER INDEX REBUILD command with the WAIT_AT_LOW_PRIORITY option also log its actions in the error log?

KILL demo

Let’s start with a KILL example. I’ll open two connection which I’ll refer to as command and victim - note the victim’s session ID (SELECT @@SPID). For me, it’s 52

Then simple run KILL 52 from your command session.

You can then read and filter the error log with command

EXEC sp_readerrorlog 0, 1, N'kill'

And this is the output.

Killed session as it shows in the error log

WAIT_AT_LOW_PRIORITY demo

For the second demo, we’ll:

  • Create a table with a named PK
  • Fill it with some rows
  • Start reading transaction in the victim session
  • Run ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY and ABORT_AFTER_WAIT = BLOCKERS
  • Check error log again to see if it was logged
DROP TABLE IF EXISTS dbo.TestTable
CREATE TABLE dbo.TestTable
(
    Id int
    , Filler char(100)
    , CONSTRAINT PK_dbo_TestTable PRIMARY KEY CLUSTERED (Id)
)
; -- Previous statement must be properly terminated
WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
, tally AS (SELECT TOP (5000) n FROM Nums ORDER BY n)
INSERT INTO dbo.TestTable WITH (TABLOCKX)
  (Id, Filler)
SELECT 
      n
    , CAST(n AS char(100))
FROM tally 

Now in the victim sesions let’s open a new reading transacion. Since we killed the last victim let’s note the session id again. For me it’s 66

BEGIN TRAN
    SELECT * FROM dbo.TestTable (HOLDLOCK)

Back in the command session we’ll run the ALTER INDEX. This will run for a minute.

ALTER INDEX [PK_dbo_TestTable] 
ON [dbo].[TestTable] 
REBUILD WITH 
(
    ONLINE=ON 
    (
        WAIT_AT_LOW_PRIORITY
        (
            MAX_DURATION = 1 MINUTES
            , ABORT_AFTER_WAIT = BLOCKERS
        )
    )
)

We can check the error log with the same query as before and the result is:

Abort blockers shows up in error log

Conclusion

This proves that it’s being logged. The information in the log should be enough to trace the culprit (if needed).

Thank you for reading.

Original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating