belle’s sql musings

What to do if you accidentally lock yourself out of SQL Server (SINGLE_USER mode)?

Posted in DBA Toolbox / T-SQL Scripts, Issues/Troubleshooting by belle on October 17, 2008

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: