Are ABORT_AFTER_WAIT's victims logged?

Tom

2024-05-18

2 minutes

Page content

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.

Debugging