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.
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
withWAIT_AT_LOW_PRIORITY
andABORT_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:
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.