Nov 282011
 

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)

  5 Responses to “Freeze and Unfreeze Write IO on a Database”

  1. Hello John,

    This was a useful DBCC command. I tested it and it works great! Thanks.

    Sankar

  2. Never knew of it. Thanks for enlightening us

    Thanks
    Nitin

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

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

  5. Awesome document, Thanks John for the nice post!

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

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

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