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:


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.

  • I am looking at the size of our txn log backups, which occur every 15 min. Since the start of the operation, each backup is about 3 mb in size. Since the disk got full, and even after we expanded the disk, the size of each txn log backup is now 1K or less, which is why I was wondering if the index operation has stalled. I get rolling back may take longer, but if it's stalled, does that mean it will never finish? – Greg Apr 20 '18 at 19:21
  • @Greg hard to tell, really. And I don't when your threshold for giving up will cross your threshold for letting a rollback finish. I don't think there is an absolute answer you can get from your peers. – Aaron Bertrand Apr 20 '18 at 19:22
  • 1
    @Greg, are you sure it's not already rolling back? Once that disk filled up, it would have killed that query. – Tara Kizer Apr 20 '18 at 19:23
  • @TaraKizer, status for that SPID is marked suspended, I assume if it was rolling back, the status would reflect that. – Greg Apr 20 '18 at 19:28

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.

  • 2
    Thanks everyone, right after I ran above query, I find that the SPID has already completed just seconds ago, and the statement failed with "txn log is full..." So I guess it was rolling back all this time. – Greg Apr 20 '18 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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