Trying to enable compression on a large table in production but we are now out of time (been running for 6.5 hours already).

What is the impact of cancelling the operation and letting it roll back?

Trying to determine if we should power through and let it finish or kill it and let it roll back. However if rolling back a 6.5 hr operation will take just as long, then we may want to just let it finish.

We tested it in QA, which has slower disks, it completed in 4 hours, so we are not entirely sure why this is taking so long.

Things to know:

  • Table size is 271 GB with approx. 30 million rows
  • At the 3 hr mark, the drive got full, we expanded it about 40 min later
  • Prevailing wait type is IO_Completion
  • sp_whoisactive shows CPU and physical_reads increasing, while reads/writes have not moved for quite some time
  • This is on SQL Server 2017 Standard Edition, Windows Server 2016

Here is our statement:


2 Answers 2


Rollback could be 2x, 4x, etc., since rollback is single-threaded even for operations that were parallel.

My advice would be to let it finish. What's your alternative? Not do it ever? If you can't rely on the estimates your QA system gave, I don't know how you could ever have the confidence to start over and expect a different result.


Do not overreact. If you cancel the operation, it may take a very long time to rollback. If you then think rebooting the server might make it stop, think again. That will just prolong your pain.

Check the results of this query to see what is happening:

SELECT der.start_time
    , der.status
    , der.command
    , der.blocking_session_id
    , der.wait_type
    , der.last_wait_type
    , der.reads
    , der.writes
    , dest.text
FROM sys.dm_exec_requests der
    CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
WHERE der.session_id = 53;

Change the 53 in the last line to the session id for the session that is doing the compression work.

The command column will say "ROLLBACK" if the session is already rolling back. It will say "ALTER INDEX" if the operation is progressing, and you should see reads and writes incrementing.

The output looks something like:

║       start_time        ║  status   ║   command   ║ blocking_session_id ║ wait_type ║ last_wait_type ║ reads  ║ writes ║                                        text                                         ║
║ 2018-04-20 14:26:37.370 ║ suspended ║ ALTER INDEX ║                   0 ║ CXPACKET  ║ CXPACKET       ║ 143263 ║     10 ║ ALTER INDEX PK_CompTest   ON dbo.CompTest   REBUILD WITH (DATA_COMPRESSION = PAGE); ║

If the operation is rolling back, you can get an estimated time of completion by adding the estimated_completion_time column to the query above. Be aware that column will not show anything for the index rebuild operation.

Details for the columns can be seen at the Microsoft Docs page.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.