0

I used SSMS to put several databases in Single User Mode. I started doing a full backup on each database and it was working sucessfully until I got to the 3rd database and I'm receiving an error telling me the database is already open. I stopped all Agent Jobs before I started and the DB is off the network. I tried putting the database back in Multi-User mode and I'm being told it's already in use.

How do I disconnect whatever is connected to it? I restarted the SQL Server instance with no luck.

3
  • 2
    Why did you put the databases in single user mode? You don't need to do that in order to take backups. If you were trying to stop people from making changes to them, why not put them in read-only instead?
    – Brent Ozar
    May 18, 2018 at 18:20
  • I'll have to find the article I read. I read that is what I should do if I am migrating a set of databases that are in sync to another instance.
    – Bill Greer
    May 18, 2018 at 18:25
  • 2
    If you put a bunch of databases into single user mode all at the same time, then connect to each one one at a time, other things will likely connect to the later databases in the meantime. This is not restricted to SQL Server Agent - it could be a variety of things. I agree with Brent, where did you read that single user mode is a method for taking backups or migrating? May 18, 2018 at 19:10

3 Answers 3

1

You can run sp_who2 or equivalent to find a connection that's using that database. Assuming you're a sysadmin on the box (and, if you restarted the SQL Server instance, I hope you are), then you should be able to kill the process that is the current single user.

I would enter the kill command in a query window, along with the command to restore multi-user mode:

KILL 123;
ALTER DATABASE <your_db_name> SET MULTI_USER;

It is still possible that someone will manage to grab the connection in between the execution of the two commands (or that the current connection will need to roll back some changes); repeat as necessary.

NOTE: You are risking wiping out someone's changes to the DB in question by killing their connection. Please pay attention to the user in question, how they're connected, and whether it seems safe to kill their connection; you may want to contact them before killing their process.

1

Putting aside the dubious utility of single user mode here, you need to be connected to the database when you put it in single user mode to prevent another session from connecting. EG

use AdventureWorks2017

alter database AdventureWorks2017
set single_user with rollback immediate
0

I totally agree with @Brent and @Aaron on their comments.

I used SSMS to put several databases in Single User Mode. I started doing a full backup on each database and it was working sucessfully until I got to the 3rd database and I'm receiving an error telling me the database is already open.

Backup operations do not take locks on user objects. ... see my answer on Backup internals - What happens when a backup job is running - in terms of locking and performance overhead in SQL Server?

How do I disconnect whatever is connected to it?

you can take your db out of single user mode using :

Select request_session_id 
From sys.dm_tran_locks Where resource_database_id=DB_ID('') -- put the db name here !

I restarted the SQL Server instance with no luck.

Do not restart sql server. Its not required.

2
  • I appreciate the advice. I'm thinking of just using the Copy wizard in SSMS and then taking the original server offline. Thoughts?
    – Bill Greer
    May 18, 2018 at 20:55
  • 1
    No .. then use dbatools dbatools.io/download - for migrating your server from old to new one.
    – Kin Shah
    May 20, 2018 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.