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)