PRINT_ROLLBACK_PROGRESS is recorded by a session that has issued ALTER DATABASE ... WITH ROLLBACK IMMEDIATE (or ROLLBACK AFTER n) and is now waiting for the user transactions it killed to finish rolling back. The thread sleeps in three-second loops, checking progress, until every victim has undone its work and the database can transition state.
The wait’s name comes from the progress messages it prints while it loops.
Is It a Problem?
It is a duration meter for a deliberate action. ROLLBACK IMMEDIATE kills transactions instantly, but rollback is not instant: a transaction that ran for two hours can take that order of time to undo, single-threaded, and your ALTER DATABASE waits for all of it. Long accumulations here mean the terminated transactions were big, exactly the situation where “immediate” surprises people.
There is also an anecdotal variant: dropping a database snapshot while many connections keep hitting it can produce the same waiting pattern.
Common Causes
ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE(the classic maintenance move) killing long-running transactions.- Big open transactions at the moment of the state change, taking their time to undo.
- Snapshot drops contending with active connections.
What To Do
- Before state changes, look at what is running:
DBCC OPENTRANorsys.dm_tran_active_transactionsshows the transactions you are about to kill, and roughly how much undo you are signing up for. - Track rollback progress on the victims with
KILL <spid> WITH STATUSONLY. - Prefer a drain (block new work, let transactions finish) over
ROLLBACK IMMEDIATEwhen giant transactions are in flight; killing them is often the slowest path to your maintenance window.
How To See It
Rank it against everything else with Get-WaitStatistics; occurrences map to specific ALTER DATABASE events, and the error log timestamps the state changes.
Part of the SQL Server Wait Types Library.
Related deep dive: LCK_M_X, LCK_M_S, and LCK_M_U Wait Types.
Leave a Reply