“A case of header corruption”
In this article, I present to you a case of database corruption. I am sure that anyone who has worked with SQL servers for a long time has had at least experienced one case where the database header got corrupted. Let me tell you that if you’ve had this problem, you will probably remember the error number: 5172.
We will see together how to fix this type of error manually. And I must say that is not too easy. Often, for a manual recovery, it is necessary to know the internal structures of the mdf and ldf files.
I want to anticipate that in this case it is much better to rely on a program specialized in data recovery, like Stellar Repair for MS SQL.
Let’s start from the beginning.
What’s the Error 5172? This error means that the database header is corrupt and contains one or more invalid values.
So, what is the header?
Trying to be as clear as possible, a SQL Server database consists of two or more physical files. There will be at least one file with the extension mdf (called primary) which contains the data and one file with the extension ldf which contains the transaction log.
The files are split into pages that have a fixed size of 8 KB and are the basic unit of the I/O operation.
The first pages have special meanings. For example, page 0 is the header.
|Page 0||File header page|
|Page 1||Page 1 PFS page|
|Page 2||Page 1 GAM page|
|Page 3||The first SGAM page|
|Page 4||Not used|
|Page 5||Not used|
|Page 6||First DIFF mapping page|
|Page 7||First ML mapping|
Each page is then divided into a header and a body. The header of each page has a size of 96 bytes, the remaining space of 8096 bytes contains data and is called body.
The header contains important information, such as the page type (m_type) that identifies what type of data the page will contain.
For example, a value of m_type equal to 15 identifies a page of type header.
Type this command:
DBCC TRACEON (3604) DBCC PAGE ('Empty', 1, 0, 3)
Now that you know the theory, let’s see how to fix the database.
Fix Corrupt Database Manually
The scenario: Your client has no backup and has just sent you its two corrupt mdf and ldf files.
You tried to attach the files with the following command.
EXEC sp_attach_db @dbname = N'Db_5172', @filename1 = N'C:\scambio\Db_5172.mdf', @filename2 = N'C:\scambio\Db_5172_log.ldf';
But you got this error:
Ok! We have the 5172 error: The header (page 0) of the ldf file is corrupted.
If you open the ldf file with a hex editor, you can see that the first part of it contains only zeros.
Well, the transaction log is gone!
In this case, the only way to recover the data is this:
- Create an empty database and then put it offline:
CREATE DATABASE Db_5172 ALTER DATABASE Db_5172 SET OFFLINE
- Then, replace the files with the damaged ones.
- Now try to put the database online with the following command:
ALTER DATABASE Db_5172 SET ONLINE
- You get the database attached but in Suspect Mode.
- Now to repair a database in Suspect Mode, set the database in Emergency and Single User Mode:
ALTER DATABASE Db_5172 SET EMERGENCY; ALTER DATABASE Db_5172 SET SINGLE_USER
- And finally recover the database.
DBCC CHECKDB (N'Db_5172', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
Without any other problem, you will have your data back. Obviously without the changes contained in the transaction log. The ldf file is damaged and will be lost.
Recover Database with Stellar Repair for MS SQL
Stellar Repair for MS SQL can effectively repair a corrupted database with 5172 error. The software will read all the data inside the non corrupted mdf file. The transaction log can be missed.
Let’s execute the Stellar Repair for MS SQL software.
- Just select the mdf file to recover.
- Now press the Repair button.
- Choose the Advanced Scan option and press OK.
- The table is recognized.
- Press the Save button to save the recovered data in a new database.
- Insert the server name and the authentication mode and press the Next button.
- Choose the Standard Saving method and press the Save button.
- The software will start to write the data into a new database.
- At the end, you will find a new database named Recovered_Db_5172.
I hope you will find this post useful.