smt0
asked on
Cannot drop the database because it is currently in use. Cannot kill process.
I have a SQL 2000 server and a database that I want to drop.
A drop command returns "Cannot drop the database 'MyDB' becuase it is currently in use.
I tracked down the process stopping the drop and tried to Kill it, but this had no effect. I am fairly sure this is a transaction that got left open when an application crashed out as it is quite old and the PC involved has been re-booted several times since then.
So I tried 'ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE' to try and force the issue, but this command runs but never completes.
How can I clear this open transaction and/or drop the database without having to re-boot the server?
A drop command returns "Cannot drop the database 'MyDB' becuase it is currently in use.
I tracked down the process stopping the drop and tried to Kill it, but this had no effect. I am fairly sure this is a transaction that got left open when an application crashed out as it is quite old and the PC involved has been re-booted several times since then.
So I tried 'ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE' to try and force the issue, but this command runs but never completes.
How can I clear this open transaction and/or drop the database without having to re-boot the server?
Stop the SQL Server Service and try deleting..it sholud get deleted....
if possible try to stop the server and 'DROP
otherwise use sp_who and find the processIds which are using that particular db, kill all the connections , and try to drop the DB from the 'Master' db
ASKER
I want to do this without stopping the SQL Server Service if possible as this is a production server.
As I said in my post, I have tried issuing the KILL command to the relevant SPID but this fails and returns the message "SPID 109: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds."
As I said in my post, I have tried issuing the KILL command to the relevant SPID but this fails and returns the message "SPID 109: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds."
In order to maintain transactional consistancy, SQL must undo any work done in a transaction when a process is killed. This is called Rollback and typically takes as long as the original transaction. This wil be alright Soon. You should wait till it rollbacks
ASKER
The original transaction that is causing this issue was a CREATE VIEW statement and so I wonder why this should take such a long time to roll-back? I have been issuing kill statements to this process for about an hour now and no luck, so I am not confident that waiting will bring a result. However, in the absence of any alternative, I'll wait some more...
Does that involve distributed Queries or the database is used in replication ? in these senarios i found the rollback status was going upto 4 hrs
Are you connected to the master database when you issue drop database or are you connected to the database you want to drop?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi smt0 ,
Right Click your Database in Enterprise Manager
All Tasks
Detach Database
Click on Clear Button (will kill all existing connections)
Click on Cancel
Drop Database
Right Click your Database in Enterprise Manager
All Tasks
Detach Database
Click on Clear Button (will kill all existing connections)
Click on Cancel
Drop Database
run sp_who2 'active'
then get the spid
and run dbcc inputbuffer (@spid)
to identify the blocking process
then get the spid
and run dbcc inputbuffer (@spid)
to identify the blocking process
smt0:
Did my solution help you?
Did my solution help you?
ASKER
Yes thank you,
The SPID was related to view on a linked server, only problem was , we had already dropped the other table on the linked server.
so we could not kill it. In the end we stopped and restarted overnight.
The SPID was related to view on a linked server, only problem was , we had already dropped the other table on the linked server.
so we could not kill it. In the end we stopped and restarted overnight.