Do you know you can suspend the write IO on one of your database and resume them later? 2 undocumented DBCC command allow you to do so, DBCC Freeze_IO and DBCC Thaw_IO. When DBCC Freeze_IO is called all the IO performing writing operation will be suspended immediately (not terminated). Once the IO is frozen, you can treat the database as a read-only database. Any writing activity will not return like you write data to a readonly database, they hang until you unfreeze the io by calling DBCC Thaw_IO.
In one session, you run following code. I assume it will take one or half minute.
use AdventureWorks2008R2 begin transaction delete from sales.SalesOrderDetail rollback
You then immediately go to another session and run
Then, you will see the first session still running but never return. You are able to read the database. You can also check use sp_lock @@FirstSessionID to check which page of the table it’s deleting. But you will never see the first session to delete next data page. To resume the IO, you can just call
Such functionality is not useful for your day to day work. But it will be useful when you need a snapshot of current IO and lock status
dbcc freeze_io(AdventureWorks2008R2) --code for taking a snapshot of status dbcc thaw_io(AdventureWorks2008R2)
5 thoughts on “Freeze and Unfreeze Write IO on a Database”
This was a useful DBCC command. I tested it and it works great! Thanks.
Never knew of it. Thanks for enlightening us
First time I’ve come across an example of these two dbcc commands being used.
Could be useful for troubleshooting locks, waits etc when extended events cannot do the job.
Nice one John 😉
We have to freeze database in a Veritas Replication environment, Veritas use a backup to a virtual device to freeze, I guess these two commands are a better solutio. I will test
Awesome document, Thanks John for the nice post!