Drop database Error: ALTER DATABASE failed because a lock could not be placed on database

I was trying to drop a database from my development environment. But I was getting the following error.

Error:

Alter failed for Database ‘AdventureWorks’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

ALTER DATABASE failed because a lock could not be placed on database ‘AdventureWorks’. Try again later.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)

 

Resolution: 

This means there are some process already locked the database and you cannot drop it before that process release the lock.

To see which process is locking the database. Run the following  command

exec sp_who2

This will list all processes and uid that has a lock on databases.

In my case, I can see PID 52 was holding the AdventureWorks database. As it is my Dev environment and i know that nothing will harm any other system if I kill this process and eventually drop the database, I issued the following command.

kill <PID> 

KILL 52 //In my case 

After you have kill all the processes that are holding a lock in your database, you will be able to drop the database.

 

 

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: