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.

  • 1
    Can you just detach the database? – ganders Aug 13 '13 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? – SQLRockstar Aug 13 '13 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 '13 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 '13 at 13:49
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

| improve this answer | |
  • 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. – Aaron Bertrand Aug 13 '13 at 17:53
0

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

| improve this answer | |

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.