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 FILEHEADERThe “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)
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
Solutions
- 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.
- 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/