Problem:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database <your db name> 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."

Scenario:

Sometimes you need to have "exclusive" access to the database, let’s say,

• when you want to force a restore, or
• when you want to alter the database and an exclusive lock is required

To get this exclusive access, you may need to set the database to SINGLE_USER mode:

USE [master]

GO

ALTER DATABASE [DBName]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

However, what if you accidentally lock yourself out of the database?

What if for some reason there is some other connection or user that is using the database, you will most probably get this error :

"Msg 5064, Level 16, State 1, Line 1

Changes to the state or options of database <your db name> 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."

How do you get yourself back in?

Resolution:

1. Determine which connection is actually locking you out

-- look for the connection that uses your db and note the spid

EXEC sp_who

2. Kill the connection

KILL <spid>

3. Change the database back to MULTI_USER

ALTER DATABASE [DBName]

SET MULTI_USER;

GO

That should do it.

Be Sociable, Share!

• 
• 

Related posts:

1. Follow up on Issue: Server principal is not able to access the database under the current security context …
2. tablediff.exe – Compare and Synchronize your SQL Server tables …
3. Script to Drop/Recreate CHECK Constraints …
4. Drop and Re-Add Server Registration When Renaming Computer …
5. SQL Server PowerShell : How to List SQL Server Services using PowerShell …
6. SQL Server 2005 Cheat Sheet …