Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Aug 13 '13 at 17:03

This question came from our site for professional and enthusiast programmers.

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

2 Answers 2

up vote 5 down vote accepted

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

share|improve this answer
1  
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
    
Thank you. This i believe might help. –  knahs Aug 14 '13 at 13:49

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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