CODEWITHSUNDEEP
sql-server
jeremcc
jeremcc

Reputation: 8741

How to delete database, error 5030 database can't be locked

I am trying to delete an existing database in SQL Server 2005. My first attempt produced the following error:

5030: The database could not be exclusively locked to perform the operation.

I have since killed all processes that are accessing the database. I have also removed the replication subscription that it had previously been involved in.

Any thoughts on what else that could be holding the lock on it besides SQL Server processes and replication?

Update: I restarted the server, and that fixed it. I was trying to avoid that, since this is a production server, but hey what can you do?

Upvotes: 9

Views: 30328

Answers (8)

farnoush resa
farnoush resa

Reputation: 403

This error normally occurs when your database is in Multi User mode where users are accessing your database or some objects are referring to your database. First you should set the database to single user mode:

ALTER DATABASE dbName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Now we will try to delete the database

delete DATABASE ...

Finally set the database to Multiuser mode

ALTER DATABASE dbName
SET MULTI_USER WITH ROLLBACK IMMEDIATE

Upvotes: 0

JustBeingHelpful
JustBeingHelpful

Reputation: 18980

To avoid this error, use the T-SQL script below in the master database. Make sure to run this (and modify the @dbname) for each database you are running the ALTER DATABASE command in.

"The database could not be exclusively locked to perform the operation"

This "connection killer" script will work if Windows has established JDBC connections to the database. But this script is unable to kill off JDBC connections for Linux services (e.g. JBoss). So you'll still get that error if you don't stop JBoss manually. I haven't tried other protocols, but please comment if you find out more information as you build new systems.

USE master;

DECLARE @dbname sysname

Set @dbname = 'DATABASE_NAME_HERE-PROD'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

Upvotes: 0

user3736882
user3736882

Reputation: 1

Why would we make a deleted DB to multi user mode.

ALTER DATABASE dbName SET MULTI_USER WITH ROLLBACK IMMEDIATE

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294377

A production server in which so many connections use the database yet you want to drop it? :)

None the less, how to kick out everybody from the database:

USE [dbname];
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Then drop the database:

USE [master];
DROP DATABASE [dbname];

There is still a very small window of opportunity between the USE [master]; and DROP DATABASE ... where some other connection can grab the 1 single allowed lock on the database, but it usually not worth working around that.

Upvotes: 14

dkretz
dkretz

Reputation: 37655

You don't happen to know if anyone left a transaction in an uncompleted rollback state (or otherwise uncompleted)? Might as well check the locks list, too.

Upvotes: 3

MrTelly
MrTelly

Reputation: 14875

In the management studio, goto Management->Activity Monitor (right click) -> View Processes. That will give you a full list of everything running, you can sort the list by Database to see what is still attached, and you can also kill any connections. It's easy to end up with orphaned connections that will prevent you from getting the exclusive access that you need.

Upvotes: 2

Daniel
Daniel

Reputation:

No One else should be using the DB, including yourself.

Upvotes: 1

Chris Ballance
Chris Ballance

Reputation: 34347

I hate to say it, but a quick solution is to restart the system, make sure the sql server server service is not started, then you should be able to delete.

Also, is IIS stopped if you db is connected to a web ap?

Upvotes: 6

Related Questions