Skip to main content

*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.

database is in 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.

sql server replication monitor



















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:


remove replication 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

Comments

Popular posts from this blog

Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 199 The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).

Hi guys! Here's another quick and easy post. Scenario I was trying to copy a SQL agent job from one server to another by just copying the script of that SQL job. You can copy any script by right clicking a job > Script job as > create to > new query window. I pasted the job on a different server and tried to execute it and received this error: Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 199 The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]). This error message is simply saying for the variable '@owner_login_name', the user account associated with the variable is incorrect because it does not exist. Fix Simply update the variable for '@owner_login_name' to a valid user account for the current SQL instance. Now when you execute the script again to create the SQL agent job your results will be: Command(s) completed successfully. If you have any furt...

Executed as user: . Incorrect syntax near ''. [SQLSTATE 42000] (Error 102). The step failed.

Hi everyone! Today's post is about an all too common problem... syntax issues. Whether you fat-finger your keyboard or are always in a rush, chances are you have already encountered many syntax errors. But if you are a newbie,  your syntax error is just around the corner. No worries, this is an easy fix. Syntax errors just let you know part of your code or script is incorrect. Scenario I have a SQL agent job that is using the SQL command line to perform index maintenance, but it is failing. Here's my code : sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE dbo.IndexOptimize @Databases = ' databaseName ', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @UpdateStatistics = 'ALL',@WaitAtLowPriorityMaxDuration = 180, @WaitAtLowPriorityAbortA...

How to export SQL agent jobs from your SQL Server instance

Hi everyone! As a database administrator, you always need a copy of your data and data processes for: migrating servers disaster recovery backup procedures/best practices This post covers a simple Powershell script. The script is to take a backup of all the SQL agent jobs on your SQL server instance. If you ever needed to recreate all the jobs on another server, all you would have to do is execute the SQL script. I. In SSMS (SQL Server Management Studio), create an empty .SQL file and save it with this naming convention: I.e. SqlAgentJobs_PROD01.sql } This file will represent all the SQL agent jobs on the primary production SQL server. II. Copy the "SqlAgentJobs_PROD01.sql" file from above to a network share. III. Run the Powershell script below in  Powershell ISE as administrator: ##migrating jobs from one server to another with the replace command $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionStrin...