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.
-
1Can you just detach the database?– gandersCommented 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?– SQLRockstarCommented 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 SeigelCommented 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.– knahsCommented Aug 14, 2013 at 13:49
Add a comment
|
2 Answers
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
-
2At this point you should be able to
DROPthe database, then you don't have to worry about forgetting to go shift+delete the files. Commented Aug 13, 2013 at 17:53
You must detach the DB first if you want to mess with the files, otherwise they are locked.
answered Aug 13, 2013 at 12:50
