SQL Server - Alter Database Set Single User Blocking

Asked By Chris Wood on 08-May-08 01:22 PM
Hi,

I am trying to set a database to single user with rollback to get exclusive
control so I can restore it but this command is hanging against spid -2
(orphaned DTC). Any ideas?

This is SP2 build 3239.

Thanks

Chris




Aaron Bertrand [SQL Server MVP] replied on 08-May-08 01:53 PM
Russell, Chris is at the latest CU available (build 3239).
Chris Wood replied on 08-May-08 02:02 PM
Thanks for the detailed explanation of how to fix the problem spid but I am
more concernded how a simple ALTER DATABASE command can get hung by DTC.

Chris
Aaron Bertrand [SQL Server MVP] replied on 08-May-08 02:08 PM
Setting it to single_user requires that all uncommitted transactions are
rolled back.  If you have a transaction that is hung, or uncommitted, the
alter has to wait.  Similarly if it takes a long time to roll the
transaction(s) back, the alter has to wait.  If there are existing
transactions still occurring in the database, it is pretty hard to just say

You didn't list the exact command you used, was it:

ALTER DATABASE foo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

?
Russell Fields replied on 08-May-08 02:10 PM
Thanks.  Dumb mistake.  I read 3139 instead of 32.  - RLF
Russell Fields replied on 08-May-08 02:22 PM
Chris,

I have not experienced this personally, but I have had a never-ending
rollback on a database where I could not ALTER DATABASE until I finally had
an opportunity to restart the SQL Server.  The reason is basically the same,
the spid could not be killed.

So, I suspect that the SQL Server is viewing the DTC as a state that it
cannot safely kill.  It is waiting for someone to figure out what to do.
Why does it not KILL the UOW itself?  I don't know, but presumable the SQL
Server developers decided that was not a safe thing to do.

RLF
Chris Wood replied on 08-May-08 02:43 PM
Aaron,

We are using Redgate SQLBackup 5.3 for backups and then people ask for a
backup to be restored and don't log off from the DB. I just added
ALTER DATABASE foo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Followed by
ALTER DATABASE foo SET MULTI_USER;
Followed by the Redgate Restore command in the script I run.

I had never had this sort of problem before but did wonder why the restore
was taking so long when I got a MOM alert saying my spid was blocked by
SPID -2. I know that this is an orphaned DTC transaction but didn't think
that this could happen as it did not appear that any other transaction was
accessing the DB I wanted to restore. I stopped/started DTC and all was
fine.

I had seen this http://support.microsoft.com/kb/949075 that was in build
2251 according to my TAM and is down to make SP3.

This is definitely something new to watch for.

Thanks

Chris
Chris Wood replied on 09-May-08 03:44 PM
Russell,

Happened again just now on a different server running build 3175 and the UOW
is
00000000-0000-0000-0000-000000000000

Any ideas?

Chris
Aaron Bertrand [SQL Server MVP] replied on 09-May-08 04:14 PM
Is this server participating in distributed transactions (either starting
them on remote servers, or having remote servers start them here)?  Did
restarting DTC fix the problem again?

My only other suggestion: open a support case.  If you've found a bug, it
will not cost you anything to eventually get the fix.  If they help you
discover the configuration or architecture issue that is causing it, it is
probably $299 well spent.
Chris Wood replied on 09-May-08 04:24 PM
Stop/Starting DTC is the cure. I am thinking a raising an issue thru our
premium Support Contract.

Thanks

Chris
Aaron Bertrand [SQL Server MVP] replied on 09-May-08 05:40 PM
Another thought I had, before you do that, you can try to figure out which
app(s) are actually using DTC, and investigate them (because if it's your
own application's fault, you will pay).  You can figure this out pretty
quickly... Disable DTC, and see who complains that their app just stopped
working.  :-)

I am not sure how your support contract works, but if there are costs
involved, you might want to consider trying to find it yourself first.  Just
in case.