I went through a tough time yesterday trying to delete a Database which unknowingly went into a state of Single User Mode while doing some RnD on it 😛 . I had to try many different options one by one and finally was able to delete it, which I will be sharing with you now in this blog along with images.
I started with check in the properties of the Database “Export_MSCRM”, And I encountered with the First Error:
Database is already open and can only have one user at a time.
Then I decided to delete the Database directly by right click on the selected DB -> select Delete. But then I encountered the Second Error:
Changes to the State or Options of Database cannot be made at this time. The database is in single user mode, and a user is currently connected to it.
Now the only way to come out of this loop was to convert the DB from Single User to Multiple user. I tried the three options below but all in vain !!!
1> exec sp_dboption 'Export_MSCRM', 'single user', 'FALSE' 2> alter database Export_MSCRM set offline with Rollback Immediate 3> alter database Export_MSCRM set Mutli_User with Rollback Immediate
There was yet again another Error I faced:
Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Finally the last option left to me was killing the user connection so that the DB can be set to Multiple user followed by the deletion of the DB.
The query used for this purpose is:
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginamefrom sysprocesses pinner join sysdatabases d on p.dbid = d.dbidwhere d.name = 'Export_MSCRM'
The Output showed me the user along with its spid, so my next move was to kill the same using the yet another query command.
Kill spid; -- In my case that would be kill 56
Now after killing the connection, I changed it to Multiuser mode with the commands describe earlier. Then also while deleting the DB I got the Third Error of Replication:
Cannot drop the Database because it is being used for replication.
To remove the replication issue I had to run another query:
use Export_MSCRM exec sp_removedbreplication @dbname=[Export_MSCRM] go
So Finally after executing a set of following Query when I deleted the Database, It got deleted without any further Issues. I can Summarize the Query in the pic below.
Hope that will be Helpful for any user facing the similar challenges.