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

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)
Freeze and Unfreeze Write IO on a Database

You May Also Like

5 thoughts on “Freeze and Unfreeze Write IO on a Database

  1. 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 😉

  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.