*Msg 5064, Level 16, State 1, Line 1 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. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

Hi everyone!

This post will cover the single-user mode error for a database. There will be mention of replication, but that is not the focus of the post.

If you would like more information on replication, check out my other post here .

Here's what happened. I was tasked with setting up replication between two databases on a server. It turns out replication was no longer needed and I didn't need the database at all. So I dropped the replication publication and subscription without removing them from the database first.

This is a no-no. You should always remove replication from your database first before deleting any publication(s) and/or subscription(s).

Since I did not do it properly, the database is still associated with the replication process and knows the process is broken.

What did it do? It forced the database into single user mode.

Now I have to get this database out of single user mode so I can remove replication and delete the database altogether.

I.
Open the replication monitor:

In SSMS, right click Replication > Launch Replication Monitor

You will be able to see that the process is broken.

You will now have to remove the current server from under the 'My Publishers' area of replication. You will be prompted to confirm that you want to remove the publisher:

II.
You still need to get the database out of single user mode.

We need to figure out who is using the database.

'EXEC sp_who2' will show you what user is logged in to the database.

Once you find the user, you will have to kill that process with the command 'KILL spid#'.

The process that was using the database was process 60. The command I will use to stop the process is 'KILL 60'.

I received the confirmation that the process is deleted:

Command(s) completed successfully.

III.
Next, we need to see if we will be able to run any alter statements against the database so we can put the database back to multi-user mode.

Note:
As soon as you finish running the KILL command from step 2 above, you will immediately need to run these commands:

USE master;
GO
ALTER DATABASE databaseName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE databaseName
SET MULTI_USER;
GO
Your database is now back in multi-user mode.

IV.
Finally, let's get the database deleted.

First, remove replication from the database.
In my environment I am using a stored procedure that will remove replication objects from the database. 'EXEC dbo.usp_Replication_Drop'

You could also use this command:

Use databaseName;
go
sp_removedbreplication 'databaseName'
go

If successful,
 you will receive confirmations similar to these:

Dropping Replication from databaseName.
Dropping All Subscriptions
Dropping All Publications

The replication option 'publish' of database 'databaseName' has been set to false.

You will be able to delete your database now:

USE master ;  

GO  

DROP DATABASE databaseName;  

GO  

That's it!

If you have any further questions, please post below.

Thank you and Happy reading,

-marshé hutchinson
#learnSQLwithme