Tuesday, 14 July 2015 00:00

Repair SQL file header corruption

Rate this item
(0 votes)

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.

Reference: http://blog.sqlexpert.pl/2015/06/04/repair-sql-file-header-corruption/

Last modified on Friday, 17 July 2015 09:42
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Get Help Now

Thank you for contacting us.
Your Private Investigator will call you shortly.