Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Alter Database statement blocked Expand / Collapse
Author
Message
Posted Monday, August 4, 2008 11:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, June 1, 2013 12:57 PM
Points: 1,084, Visits: 94
This morning, I planned to refresh one of my development databases using a database restore. In order to restrict access to the database as I start the restore, I use ALTER DATABASE to set the database to single_user mode, specifying the ROLLBACK IMMEDIATE option. This typically works fine.

When I ran it this morning, I noticed that the alter was taking a long time. When I ran sp_who2, it showed my spid blocked by -2 and in a suspended status. I thought this might be due to a large rollback, but DBCC OPENTRAN showed no open transactions in this database.

Does anybody have any thoughts on why I was being blocked and put into a suspended status? Thanks in advance for your help.
Post #546275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse