Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm not sure whether this was an actual problem or just a normal situation where I needed to wait for rollback to finish. In either case, some insight would be appreciated.

Background details

I manage a server running SQL Server 2008 R2 SP2 that has a fairly large table:

  • 842 million rows at ~108 bytes each
  • Clustered index (85 GB)
  • 1 nonclustered index (8 GB)
  • Page compression applied to both indexes

Admittedly, table design and change control in my current working environment are not good. Someone ran the following batch to convert 9 decimal columns to numeric (18,6) NULL:

ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col1] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col2] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col3] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col4] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col5] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col6] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col7] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col8] NUMERIC(18,6) NULL
ALTER TABLE [MySchema].[My_Table] ALTER COLUMN [Col9] NUMERIC(18,6) NULL

I think the purpose of the changes was purely for standardisation of data types across tables.

This ran for close to 24 hours and got partway through modifying Col4, at which point I killed it.

What happened after I killed the query

Four hours after killing the query, the row for the session in sys.dm_exec_requests showed a status of 'running', a command of 'KILLED/ROLLBACK' and a value of 0 for percent_complete.

I could tell the session was doing something because reads, writes, and CPU usage were still accumulating at a constant rate. The query was not blocked or waiting. There was little other activity on the server and I/O associated with this query was completing in <1ms according to Process Monitor.

Why what sys.dm_exec_requests showed seemed odd to me

This is confusing given that if I look at a killed query on another 2008 R2 server, sys.dm_exec_requests shows the session in a status of 'rollback', a command of 'AWAITING COMMAND', and a number reported for percent_complete.

The only difference I can find between these servers is that the former is 2008 R2 SP2 and the latter is 2008 R2 SP2 CU8.

The actual question!

What accounts for the difference in the way these rollbacks are reported in sys.dm_exec_requests? For example, does a status of 'running' with a command of 'KILLED/ROLLBACK' indicate that the KILL command is still being processed, whereas a status of 'rollback' indicates that the query has progressed to performing the actual rollback?

The query finally rolled back, by the way. I never saw the status transition to rollback or a percent_complete reported even though I'm capturing sp_whoisactive output every 5 seconds.

share|improve this question
1  
I wrote about this a long time ago and, at the time, I was seeing rollbacks display a percent_complete value - however, it likely depends on what operation is being rolled back, exactly. I've also filed a couple of Connect items: connect.microsoft.com/SQL/feedback/… connect.microsoft.com/SQL/feedback/… connect.microsoft.com/SQLServer/feedback/details/641790 connect.microsoft.com/SQL/feedback/details/354545 –  Aaron Bertrand Oct 11 '13 at 15:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.