0

Taking too long to drop a 500gb+ SQL Server database. My question being what ways can we drop a database other than through GUI or T-SQL? Would deleting the .mdf and .ldf files from the server help? I can detach and delete the mdf and ldf files. I just want to know the fastest way to drop it. It took me close to 11hrs without any results so I killed it and was hoping someone might know how to do it.

4
  • 1
    Can you just detach the database?
    – ganders
    Aug 13, 2013 at 12:50
  • Sorry, you want to do this without using SSMS or T-SQL? Can you explain why you would not want to use either of those methods? Aug 13, 2013 at 17:08
  • @knahs: You haven't explained how you're trying to drop the database. If you used SSMS and have the "Delete backup and restore history" checkbox checked (it's checked by default) or the "Close existing connections" unchecked (again, by default), this may be what's holding up the process.
    – Jon Seigel
    Aug 13, 2013 at 17:17
  • Thats true Jon. I want the backups to be deleted so I left it untouched. I have the close exisiting connections checked. You are probably right this might be holding up the process.
    – knahs
    Aug 14, 2013 at 13:49

2 Answers 2

5

Let's say you have a database named wildlife, kick the users off and detach the database.

This can be done via the SSMS GUI, right click, under all tasks.

-- Kick off users, roll back current work
ALTER DATABASE [WILDLIFE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Detach database
sp_detach_db 'WILDLIFE'
GO

However, the *.mdf and *.ldf files are still there. Shift delete them so that they do not go to the recycle bin.

Good luck!

PS: Check out my blog for more information.
http://craftydba.com/?p=1753

1
  • 2
    At this point you should be able to DROP the database, then you don't have to worry about forgetting to go shift+delete the files. Aug 13, 2013 at 17:53
0

You must detach the DB first if you want to mess with the files, otherwise they are locked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.