February 7, 2012 at 1:39 am
Usually this is a non-issue, but my command (while using master DB) "alter database [db_name] set multi_user with rollback immediate" failed with the following error message:
"Transaction (Process ID 451) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Return the transaction."
I've also tried to drop the questioned DB, but failed with the same error message.
I've resorted to both sp_who2 and sys.sysprocesses and found that there are no actively connected processes to this DB.
So what should I do? Note: -T1222 is not enabled on this server.
Another question, what kind of locks are needed and what kind of resources are beding locked during the action of changing a DB's mode or dropping it? What can be the possible conflicts with other processes?
Thanks very much in advance!
Bazinga!
February 7, 2012 at 3:17 am
Hi,
I was faced same issue long back, restart the services than immediately execute that query. It will work...
Thanks & regards
Satish Saidapur
February 7, 2012 at 6:08 am
have you tried without the rollback immediate?
trace flags can be set on the fly.
dbcc traceon(1222,-1)
dbcc traceoff)1222,-1)
---------------------------------------------------------------------
February 7, 2012 at 5:56 pm
george sibbald (2/7/2012)
have you tried without the rollback immediate?trace flags can be set on the fly.
dbcc traceon(1222,-1)
dbcc traceoff)1222,-1)
Without "rollback immediate" didn't work either.
I've since also tried restart the sql server service but still no luck.
Finally the solution was to restart sql server with /f /T3608 and issue an sqlcmd to drop the DB and restore a recent backup.
Bazinga!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply