Question to Experts

Question

Question to Experts

raamaakrishna

SSC Enthusiast

Points: 113
More actions
August 2, 2010 at 1:58 pm

#142134

What happens to the physical database files when I run a drop command on the database?
I know they get deleted, but what kind of command does sql server runs to delete those files?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Since the OS files that make up the database get deleted, I'd say DELETE. Difference is that the files do not go to the Recycle Bin.

raamaakrishna SSC Enthusiast Points: 113 More actions · Answer 2

raamaakrishna

SSC Enthusiast

Points: 113

August 2, 2010 at 2:11 pm

#1201235

do you mean like shift+delete command?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

August 2, 2010 at 2:14 pm

#1201237

Probably. Why?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Lynn Pettis (8/2/2010)
Probably. Why?

Think maybe someone's dropped a database and is hoping to recover it?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

raamaakrishna SSC Enthusiast Points: 113 More actions · Answer 5

It is just that I am trying to understand how drop command works. If we know exactly what command is being run to delete those files may be we can recover the database even though we do not have backups...

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

raamaakrishna (8/2/2010)
I know they get deleted, but what kind of command does sql server runs to delete those files?

It'll be an API call from SQL to the operating system requesting that the files be deleted. I don't offhand know what the exact API call is, buit if you want to know, I can ask someone.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

raamaakrishna (8/2/2010)
If we know exactly what command is being run to delete those files may be we can recover the database even though we do not have backups...

You'd need to buy a tool to recover deleted files. For what it's worth, I've never seen someone do that successfully on a SQL database. In all the cases I've seen where someone tried, if they got the file back at all it was corrupt.

Take backups! The recoverability of the databases is your job as a DBA.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

raamaakrishna SSC Enthusiast Points: 113 More actions · Answer 8

Yes gail, you are right...

But I was the one who is a victim of such scenario. But at this situation, I have backups for all the database I have... First thing I do generally when I create a database is to have a back up for that db.

I am just trying to understand have a deep understanding....

raamaakrishna SSC Enthusiast Points: 113 More actions · Answer 9

Hi Gail, Can you please find out what api is called?

If possible, what is the sequence of actions that happens when you run a drop database command....

Steve Jones - SSC Editor SSC Guru Points: 738673 More actions · Answer 10

Do not ever, ever, ever drop a database without a backup. Even if it's development. Take a backup, store it somewhere, at least for a few months.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

raamaakrishna (8/2/2010)
Hi Gail, Can you please find out what api is called?

Looks like it's the DeleteFile function in Kernel32, so nothing special.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 12

All file operations in SQL Server are handled through the file managment functions in the Win32 API which are documented in the Books Online Topic File Management Functions.

This was documented by Ken Henderson in his book The Guru's Guide to SQL Server Architecture and Internals and these low level internals have been consistent from SQL 2000 onward, with the exception of some additions like Instant File Initialization which uses the SetFileValidData function which was new in Windows Server 2003, and not around for SQL Server 2000.

The Win32 function specific to deleting a file would be DeleteFile. However, I don't see how knowing this level of internals for SQL Server is going to help you recovery from a dropped database without a backup. Once the file is deleted in the file system its pretty much good luck at recoverying it without a backup. I've not seen it be successfully done yet, as Gail said most of these end up corrupt and/or damaged beyond use.

Leo.Miller SSChampion Points: 13051 More actions · Answer 13

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.

Does anyone know why Microsoft did this? It cause some issues for a client of mine when they dropped some offline databases, then tried to recreate them in the same location, only to get "file already exists" errors. At the same time they couldn't understand why dropping the databases didn't free up diskspace.

Took a while to work out what was going on there and why the files were not being deleted.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 14

I don't definitively know why Microsoft made the engine work that way, but I do know that SQL Server doesn't have a file handle to an Offline Database, so its not guaranteed that maybe you didn't set the database Offline in one Instance and attach the database files to another instance of SQL where the database is Online (yes I have done this before but it was an abnormal scenario that led to this configuration temporarily). With the database online, the files are locked and guaranteed in place. Offline closes the database, and you could move the files or even delete them from disk manually, so the pointers in the sysdatabases, sys.master_files metadata views would be incorrect, and lead to an exception in calling DeleteFile. Sure they could code around all the various scenarios but there is a trade off for everything.