Resolving SQL Error 5173

This article will provide some insight of this error type :

The generic form is :

Error 5173: Cannot associate files with different databases.

There is no format specifier, so all failure in attaching any possibly corrupted database will end up with this message.

One of the causes of this error is the mismatch of some ID internally maintained by SQL Server 2000 between the MDF and LDF file.

This ID is in the form of GUID and is called binding ID in SQL Server terminology. This kind of checking is good, considering the possibility of associating the MDF with wrong LDF file and vice versa.

But sometimes, this error can be a disaster when you absolutely sure that you have already make some necessary steps to verify that it is indeed the right binding between your MDF and LDF file, yet, SQL Server still complains that is different.

In this case, you have to resort to manually verifying the internal GUID of your MDF and LDF file, the way SQL Server was doing it.

The internal GUID of MDF and LDF file is located at file header page (page 0) in the file header record. The file header record offset location is determine by the one and only slot in row offset array (slot 0), at offset 0x1FFE, word size.

Let’s have a look of our troubling MDF file to get the row offset value :

The slot 0 value :

The m_tornBits value :

Please beware that the byte at offset 0x1FFF is actually the torn bit, and in detached state, it tends to point to wrong value.

So, you should perform some correction of this byte to obtain correct row offset location for the file header record.

First, this value is masked with 0xFC with AND operation. This can be done by ubiquitous calculator program with scientific mode :

0x01 AND 01xFC = 0x00

Next the m_tornBits value 0x80102001 is right shifted by 0x1E, again this can be accomplished by calculator (make sure you checked the Inv to perform the Rsh operation, because default operation is Lsh) :

0x80102001 Rsh (right shift) 0x1E = 0x00000002.

For those who are curious, the 0x1E value is the value that is used for torn page bits calculation. Because this is the last sector, it is right shifted with 0x1E, the previous sector with 0x1C, etc.

Further information can be obtained from my post about fixing torn page value.

This value is Or-ed with value obtained above :

0x02 Or 0x00 = 0x02.

So, the correct value is 0x22D instead of 0x12D 🙂

I will now copied this record location (offset 0x22D at page 0) to new location :

As you can see, the internal GUID is located at offset 0x3B.

Perform the same operation for page 0 for LDF file, compare and match the GUID, and re-attach the database. This time the database should be attached successfully.

Now for some warning, or in other word, doing it at your own risk notice.

By using this method, you can match the internal GUID of even different MDF and LDF file, so please make sure that you have first get rid of this kind of human error, before performing the GUID matching process.

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: