Orphaned Records in MDF File

By the meaning of orphaned records is any records that resides in MDF file but unreachable by MS SQL Server. The record is out there, but MS SQL Server doesn’t ‘see’ it.

There are no discrepancies or inconsistencies found. Checking by DBCC by every angle do not yield anything suspicious.

The possibility to find the orphaned record is depended on the nature of table design.

If some column in the table has the unique identifier that can be found physically by means of hex editor and the column’s future value can be determined, then it is possible to find and hence to recover them.

Below is sample case to illustrate this.

After the database crash is recovered, and any allocation and inconsistencies is painstakingly fixed, the existing current data is copied to the fully functional database.

But the user of the system still complaining about their missing newly created records.

The design of the table has the unique identifier of numeric datatype and is an identity. So, it is possible for me to determine the latest record by ordering by this unique id in descending manner :

orphan1

The latest record is at 84095. If there are the missing record, then there should exist, the records whose UID is 84096, 84097, etc.

The first thing to check is looking to the current log file for insert row operation on that UID. Let’s take 84096 for example.

To be able to find the UID in the physical file using hex editor program, I have to convert it into big endian form. In my case, the UID is physically stored as follows :

orphan2

For the above sample record, after converting from big endian format, the UID value is 0x013B64 = 80740. Now, in order to find the value 84096 physically, then :

84096 = 0x014880, convert it to big endian and padding 0x8048010000000000. By entering “018048010000000000” as search string for hex value to the current log file :

orphan3

So much of the write-ahead of MS SQL feature. Is it truly lost ? Then I decide to find it in the existing damaged MDF file.

To my astonishment, I found it in two location, because of the header detail nature of the design, first location is the detail, and the second location is the actual data :

orphan4

And if I tried to convert the offset 0x4166390 into the page id format, then this data should be located somewhere in page 1:8371. But using the DBCC PAGE :

DBCC TRACEON(3604)
DBCC PAGE (‘edoc’, 1, 8371, 3)

TDocsUID                         = 84095           

The latest record is 84095, but 84096 is not listed. So, by using this method, I recovered about 60 or so healthy records, with only one record that is truly missing, either in MDF file or the LDF file. But I believe, that if I am given the chance to search entire affected harddrive, I will find this missing record.

So, how come that the missing record is not in LDF, but instead in MDF file ? My hypothesis is that, at crash event, the panic-stricken OS kicked the MS SQL server, throw everything that’s in the cache to the harddrive. MS SQL server do not have any chance to do the write-ahead thing.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: