Repair SQL file header corruption

Microsoft SQL is the preferred choice when it comes to selecting a Relational Database Management System (RDBMS) for an organization. However, just like other RDBMS, it is plagued by several errors – corruption being a major one. SQL database corruption has many facets and there are quite a few places where the corruption can occur.

This article will be focusing on file header corruption in MS SQL database and also explain its solution. But first, let us start with the basics.

What is a database File Header Page?

The fundamental unit of data storage in SQL Server is called a Page.An SQL data file has the extension .mdf or .ndf and the disk space allocated to a data file is logically divided into pages numbered contiguously from 0 to n. Disk input – output (I/O) operations are performed at the page level. That is, SQL Server reads or writes whole data pages.

The structure of an SQL Server data file is divided into the following pages:

  • Page 0: Header
  • Page 1: First PFS
  • Page 2: First GAM
  • Page 3: First SGAM
  • Page 4: Unused
  • Page 5: Unused
  • Page 6: First DCM
  • Page 7: First BCM

As shown above, the first page of the SQL data file is the “File Header”. This page occupies about 8 KB of storage space and it stores metadata about that particular SQL data file. All the files have a header into the page number 0 and it is not recoverable by CHECKDB. In case of damage or corruption in the file header, you must restore the entire file. As with the boot page, you can look at the contents of the header with the “DBCC PAGE” command and it will interpret all the fields for you. Alternatively, you can use the “DBCC FILEHEADER” for this purpose command, which actually does a better job.

DBCC FILEHEADER

The “DBCC FILEHEADER” command when executed on a database returns a tabular output containing various fields indicating information about the database. The command takes two parameters, the first being the database name or database id (passing 0 means you want to run this on the current database) and the second is the file id.

Here is the basic syntax of the command:

DBCC FILEHEADER (‘DBName’, ‘FileId’);

GO

Some of the information returned in the output is explained below:

  • Growth: This indicates the number of pages to grow the file by. It is indicated as a number if the 0×100000 bit is NOT set in the Status field. If it is set, the Growth is indicated in percentage.
  • BindingId: This is used to make sure that a file is really a part of this database
  • Status: This indicates the kind of file and the state it is in (e.g. 2 = regular disk file)
  • SectorSize: This is the sector size of the disk.
  • Various sizes in number-of-8kb-pages (e.g. MaxSize of -1 means file growth is unlimited)

Effects and Reasons of SQL File Header Corruption

The above information clearly highlights the importance of the SQL file header page. Hence, quite expectedly, any damage or corruption to this page has the potential to render the database dysfunctional. Users may experience inconsistency in data or complete inaccessibility of the database.

Though all corporates would take appropriate measure to safeguard the valuable data stored within the database, the following unexpected reasons could still lead to file header page corruption:

  • Problems with the drivers and controllers
  • Sudden power outages
  • Sudden rebooting of SQL server

As such, every corporate must have proper methods ready at hand to recover from such a disaster if it may arise.

Solutions

  1.  By far, the best and safest approach is to perform a complete restore of the database from the backup. This is why having the backup of a database is extremely important. However, if you don’t have a backup, you might have to resort to one of the following solutions.
  2. The following methods does seem to work but only in some  situations:
    • Stop SQL Server instance
    • Copy MDF and LDF files to another location
    • Delete original MDF and LDF files
    • Start SQL Server instance again
    • Create new database with exact same name and file names
    • Stop SQL Server
    • Overwrite newly created MDF and LDF

After this the database should be back online. If it is, put it into EMERGENCY and SINGLE USER mode. Lastly, execute DBCC CHECKDB as follows:

DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

Caution: Executing the DBCC CHECKDB command with flags such as REPAIR_ALLOW_DATA_LOSS almost always results in the loss of some data so use it as a last resort.

If you do not have a backup of the database and cannot risk losing your precious data, your safest bet would be to opt for a third party SQL Database Recovery tool like Stellar Phoenix SQL Database Repair. It is a safe and reliable way of repairing the corrupted database file and restoring all your precious data.

Final Words
Some errors cannot be anticipated and you should come to terms with the fact that an SQL database can get corrupted without warning. Hence, always having a complete backup of the database is the only way to stay safe. However, if you have landed up in a soup, great third party software can help you to recover your corrupted database in no time.

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



5 thoughts on “Repair SQL file header corruption

  1. Pingback: Repair SQL file header corruption | Stellar Dat...

  2. I have a company database, which I recovered from a crashed hard disk, everytime I yry to fix it. I keep getting an error indicating that the header is damaged, database is inacessible, please wat can I do to fix it.

    • Hi Pascal,

      Download Stellar Phoenix SQL Database Repair tool which I mentioned in the article.The software is able to repair corrupt header.

      Note: Take a backup of your database before going for any approach.

      Good Luck!

Leave a Reply

Connect with:
  • This field its required.
  • This field its required.
    • Message is required