Solved

Cannot drop database "MYDB" because it is currently in use.

Posted on 2015-01-26
16
251 Views
Last Modified: 2015-01-30
Hi..
trying to drop a DB... can't take it offline.. just runs and runs...

lock could not be placed... don't see processes..

any ideas

Cannot drop database "MYDB" because it is currently in use.
0
Comment
Question by:JElster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 2
16 Comments
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 150 total points
ID: 40570462
Are you trying to drop it from the GUI or from a DDL statement? Odds are you have a connection open to it, so you will need to close that first - or kill it.

You can run the following query to see who or what is connected to it:
select original_login_name, db_name(database_id) from sys.dm_exec_sessions
where db_name(database_id) = 'MyDB'

Open in new window

0
 
LVL 1

Author Comment

by:JElster
ID: 40570475
nobody else is in
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40570494
nobody else except you, or nobody at all?
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 1

Author Comment

by:JElster
ID: 40570500
In this particular DB

ALTER DATABASE failed because a lock could not be placed on database ''. Try again later.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40570502
You need to close all existing connections. There's a checkbox that by default is unchecked so you need to check it before delete the database.

Capture.PNG
0
 
LVL 1

Author Comment

by:JElster
ID: 40570522
Get database in use...... ???????????????????  even when selecting close existing connections
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40570540
Ok. I also prefer to do it by script that by GUI :)

In SSMS open a new query a run the following script:
USE [master]
GO
ALTER DATABASE MYDB SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE MYDB
GO

Open in new window

0
 
LVL 1

Author Comment

by:JElster
ID: 40570554
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'ONBOARD'. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3702, Level 16, State 4, Line 1
Cannot drop database "ONBOARD" because it is currently in use.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40570560
What do you have running on that database?
0
 
LVL 1

Author Comment

by:JElster
ID: 40570573
The server has other DB's... nothing is running or accessing this particular DB
????????
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40570583
Can you check by running sp_who2?
0
 
LVL 1

Author Comment

by:JElster
ID: 40570597
nobody is in this db
0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 350 total points
ID: 40570700
Ok, then the question that I should made at the first time:
- What happened for you want to drop the database?

Also, can you post here the result from the following query?
select
    l.resource_type,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    r.command,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    request_sql_text = st.text,
    s.program_name,
    most_recent_sql_text = stc.text
from sys.dm_tran_locks l
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
left join sys.dm_exec_sessions s
on l.request_session_id = s.session_id
left join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
where l.resource_database_id = db_id('ONBOARD')
order by request_session_id

Open in new window

0
 
LVL 1

Author Comment

by:JElster
ID: 40570723
DATABASE      S      GRANT      137      KILLED/ROLLBACK      suspended      0      LOGBUFFER      327845604            NULL      Microsoft SQL Server Management Studio - Query
DATABASE      S      GRANT      164      NULL      NULL      NULL      NULL      NULL      NULL      NULL      Microsoft SQL Server Management Studio - Query
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 350 total points
ID: 40570738
So, you already have a ROLLBACK running. You can't do nothing but wait for the rollback finish.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40579231
How long the rollback took?
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question