August 2, 2010 at 8:40 pm
Jonathan Kehayias (8/2/2010)
I don't definitively know why Microsoft made the engine work that way...g.
Bug, crappy programming and testing.
August 2, 2010 at 9:18 pm
Steve Jones - Editor (8/2/2010)
Jonathan Kehayias (8/2/2010)
I don't definitively know why Microsoft made the engine work that way...g.Bug, crappy programming and testing.
Maybe, but its easy to be critical from the outside looking in. It's really the simplest design the way its built given the complexities that might exist when working with a Offline database. The counter argument could be made that DROP DATABASE should just perform a detach, leaving the physical delete of the files from the file system up to the DBA/Sysadmin to do, removing the problem that spurred this post completely because then it is a very clear and concise decision that lead to the files actually being deleted.
Consider that a Offline Database being dropped required the files to exist and be unlocked to actually perform the DROP. That adds a whole other complexity to the mix in the event that the files no longer exist for some other reason like a lost SAN LUN or DAS array.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
August 2, 2010 at 11:13 pm
Leo.Miller (8/2/2010)
An odd bit of behaviour I've noted with dropping databases is that, if the database is OffLine when you drop it, the physical files are not deleted.
It's not odd behaviour. It's intended behaviour and it's documented to work that way.
From Books Online (DROP DATABASE)
Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. To remove a database from the current server without deleting the files from the file system, use sp_detach_db.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2010 at 11:14 pm
Steve Jones - Editor (8/2/2010)
Jonathan Kehayias (8/2/2010)
I don't definitively know why Microsoft made the engine work that way...g.Bug, crappy programming and testing.
In this case, design decision, intended behaviour.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply