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
dbcc freeze_io(AdventureWorks2008R2)
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
dbcc thaw_io(AdventureWorks2008R2)
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)
Hello John,
This was a useful DBCC command. I tested it and it works great! Thanks.
Sankar
Never knew of it. Thanks for enlightening us
Thanks
Nitin
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!