Unable to drop a database

I am trying to drop a database in SQL Server 2014 which is permanently 'In Recovery'.

So I found these instructions on the internet:
USE MASTER
GO
ALTER DATABASE AdventureWorks2016CTP3
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE AdventureWorks2016CTP3
GO

But that failed with these error messages:
Msg 5011, Level 14, State 7, Line 3
User does not have permission to alter database 'AdventureWorks2016CTP3', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 3702, Level 16, State 4, Line 5
Cannot drop database "AdventureWorks2016CTP3" because it is currently in use.


Even if I go directly to the drive where the database data is situated it won't allow me to delete it there.

Please help !
Philip van GassDatabase Developer/AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
Try logging in to the SSMS console with the "sa" or other sysadmin-level account.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
This just happened to me last week.

Try running  ssms with administrator privileges. (right click -> run as administrator)
0
Scott PletcherSenior DBACommented:
You need to make sure that you are the single user in the db when you set it to single_user mode.  Although actually, I've had much better luck setting the db OFFLINE.  But here's the S_U method:

USE AdventureWorks2016CTP3
GO
ALTER DATABASE AdventureWorks2016CTP3
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE master;
GO
DROP DATABASE AdventureWorks2016CTP3
GO


And here's the OFFLINE method:

USE master;
GO
ALTER DATABASE AdventureWorks2016CTP3 SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2016CTP3 SET ONLINE;
DROP DATABASE AdventureWorks2016CTP3;
GO
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Philip van GassDatabase Developer/AdministratorAuthor Commented:
Hi Scott,

The first set of instructions gave me these messages:
Msg 922, Level 14, State 1, Line 1
Database 'AdventureWorks2016CTP3' is being recovered. Waiting until recovery is finished.
Msg 5011, Level 14, State 7, Line 3
User does not have permission to alter database 'AdventureWorks2016CTP3', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 3702, Level 16, State 4, Line 8
Cannot drop database "AdventureWorks2016CTP3" because it is currently in use.

And the second set gave me these messages:
Msg 5011, Level 14, State 7, Line 3
User does not have permission to alter database 'AdventureWorks2016CTP3', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 7, Line 5
User does not have permission to alter database 'AdventureWorks2016CTP3', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.
Msg 3702, Level 16, State 4, Line 6
Cannot drop database "AdventureWorks2016CTP3" because it is currently in use.
0
Scott PletcherSenior DBACommented:
Can you set it to EMERGENCY mode?:

ALTER DATABASE AdventureWorks2016CTP3 SET EMERGENCY WITH ROLLBACK IMMEDIATE; /* SQL may or may not accept the WITH ROLLBACK after EMERGENCY */

If that works, you should be able to drop the db then.
0
Philip van GassDatabase Developer/AdministratorAuthor Commented:
That instruction gave me these messages even though I have set the login to the sysadmin role:

Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database 'AdventureWorks2016CTP3', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
0
ScriptAddictCommented:
try taking the database offline first.

(Ya I didn't read everything)
0
Philip van GassDatabase Developer/AdministratorAuthor Commented:
I tried many different ways to solve this problem and in the end none of them worked so I had to uninstall the entire SQL Server 2017. It was an exasperating exercise and I felt like I was going round in circles. The database AdventureWorks2016CTP3 gave me problems right from the time that I installed it, because I was continually deleting and restoring it . I think the final deadlock situation was caused by me leaving the PC on overnight instead of closing it down. Thanks to everyone for taking the time out to make their contributions.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScriptAddictCommented:
Doh!

If I was in that situation I'd have killed the SQL service and gone and deleted the database manually from the file structure.  

It isn't pretty but it always gets the job done.
0
ScriptAddictCommented:
If It was a broken database I needed I would have restored it from a backup, and then fired the service back up.  

(Sadly I've done this twice so far this month)  - Dang power failures/iscsi failures.
0
Philip van GassDatabase Developer/AdministratorAuthor Commented:
Hi ScriptAddict. I tried both of those methods and neither of them worked.
0
ScriptAddictCommented:
The only time I've had that not work, the database came back online as suspicious.  

When I checked it out it said something about log files not matching.  In that particular case there was two files in another random directory associated with the app that was using the database.  I had to restore those two files to the exact same backup as the database and then I was good.

At this point it is all moot.  You've got it fixed, and are again wearing your Hero shirt :)
0
Philip van GassDatabase Developer/AdministratorAuthor Commented:
Thanks ! Not sure if I deserve it. I am now busy re-installing but I see I am only allowed a 180 day free trial this time.
0
ScriptAddictCommented:
Time to dig out the product key.

Or failing that install the express version.  Unless you hit some arbitrary file size, it has all the features of the full version now.  You only have to pay to license if you hit that size limit.  (I'd check it out before hand, sorry I have no memory.  I want to say 10GB, but ya... no memory).
0
Philip van GassDatabase Developer/AdministratorAuthor Commented:
So I have uninstalled SQL Server 2017 and re-installed it with a different name, but the old name still exists on the drop down list when I connect to the database engine. How do I get rid of it ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.