Wednesday, November 26, 2014

Set DEADLOCK_PRIORITY to update Database in Single_User mode


If you need to access a Database in Single_user mode, to either take it offline or change to MULTI_USER, change the DEADLOCK_PRIORITY so your process does not get picked as deadlock victirm.

SET DEADLOCK_PRIORITY HIGH
--KILL 58   -- kill the session using the database
ALTER DATABASE [DB_NAME] SET OFFLINE WITH ROLLBACK IMMEDIATE
--ALTER DATABASE [DB_NAME] SET MULTI_USER WITH ROLLBACK IMMEDIATE
SET DEADLOCK_PRIORITY NORMAL