Issue changing SQL database from Single to Multi User Mode

2017-12-07T13:51:29+00:00 March 2nd, 2011|Cloud, SQL|

Came across an issue today where we were unable to change our SQL database from Single to Multi User Mode. The error we received when trying to do this was.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SQLServer.Connectioninfo)

Database ‘databasename’ is already open and can only have one user at a time. (Microsoft SQL Server, Error: 924

Basically this is due to a connection open on the database as specified in the above error. So the first step was to find out what was connected to do this. To do this we ran the following SQL query (add your database name in the query below)

select spid from master..sysprocesses where dbid = db_id(‘databasename‘) and spid <> @@spid

This will display the SPID name in our case it was 60.

Now we need to kill the SPID so that we are allowed to connect via running the below query (amend to your SPID Value)

Kill 60

We were then able to change the database to multi user mode.