My Adventures in Coding

March 6, 2014

SQL Server – Alter database in Single User mode to Multi User mode

Filed under: SQL,SQL Server 2005 — Brian @ 8:43 am
Tags: , , , ,

We have some test environment databases that get rebuilt nightly. The job that does the rebuild always switches the database into single user mode when it is being rebuilt. When the rebuild is finished it will switch it back to multi user mode. However, if there was a problem with a script and the job failed, it will leave the database in single user mode.

First, open a SQL Server Management Studio query window connected to database “master”.

The command to change the database back to multi user mode is:

ALTER DATABASE {InsertDatabaseNameHere} SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

However, if there is an existing process blocking this alter, you may get the error message:

“Transaction (Process ID 864) was deadlocked on lock resources with another process and has been chosen as the deadlock victim”

Since both the existing running process and the current request have the same deadlock priority, preference is given to the longer running process which is why your alter database command is chosen as the deadlock victim. So to deal with this when you run the command again, set the deadlock priority to HIGH. However, if that does not work, you will need to kill the existing connection.

To find the “spid” of the existing connection, you can use the following query:

SELECT sd.[name], sp.spid, sp.login_time, sp.loginame 
FROM sysprocesses sp 
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid  
WHERE sd.[name] = 'DatabaseNameGoesHere'

As an alternative, you can also use the command “sp_who” to get the “spid” of the open connection:

exec sp_who

So, here is our revised command:

KILL SpidToKillGoesHere
GO
SET DEADLOCK_PRIORITY HIGH
GO
ALTER DATABASE DatabaseNameGoesHere SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

That should do it!

Advertisements

11 Comments »

  1. Thank you, You helped me.

    Comment by nghia — June 4, 2014 @ 10:33 pm | Reply

  2. Reblogged this on Nilsandrey's Weblog.

    Comment by nilsandrey — October 31, 2014 @ 2:38 pm | Reply

  3. thank you. it helped me similar issue.

    Comment by umasankar — September 3, 2015 @ 3:07 am | Reply

  4. […] SQL Server – Alter database in Single User mode to … – Mar 06, 2014  · SQL Server – Alter database in Single User mode to Multi User mode […]

    Pingback by Running Sql Server 2008 In Single User Mode | Sanchhay — May 24, 2016 @ 1:39 pm | Reply

  5. Tks! Helped me!

    Comment by eliel — May 25, 2016 @ 11:41 pm | Reply

  6. […] SQL Server – Alter database in Single User mode to … – Mar 06, 2014  · SQL Server – Alter database in Single User mode to Multi User mode […]

    Pingback by Sql 2005 Database Single User Mode | Sufferband — June 6, 2016 @ 5:11 pm | Reply

  7. […] SQL Server – Alter database in Single User mode to Multi … – Mar 06, 2014  · SQL Server – Alter database in Single User mode to Multi User mode […]

    Pingback by Sql Server Multi User Mode Script | Sufferband — June 12, 2016 @ 8:01 pm | Reply

  8. […] SQL Server – Alter database in Single User mode to Multi … – Mar 06, 2014  · SQL Server – Alter database in Single User mode to … – Mar 06, 2014 · SQL Server – Alter database in Single User mode to Multi User mode […] […]

    Pingback by Single User Server | Dumamey — June 14, 2016 @ 3:54 am | Reply

  9. […] SQL Server – Alter database in Single User mode to Multi … – Mar 06, 2014  · SQL Server – Alter database in Single User mode to Multi User mode […]

    Pingback by Sql Server Single User Mode 2008 | Sufferband — June 22, 2016 @ 1:40 am | Reply

  10. […] SQL Server – Alter database in Single User mode to Multi … – Mar 06, 2014  · SQL Server – Alter database in Single User mode to Multi User mode […]

    Pingback by Sql Database Single User Mode Script | Sufferband — June 27, 2016 @ 12:55 am | Reply

  11. […] SQL Server – Alter database in Single User mode to Multi … – Mar 06, 2014  · SQL Server – Alter database in Single User mode to Multi User mode […]

    Pingback by Single User Sql Server 2008 | Dumamey — July 6, 2016 @ 2:53 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: