Summary: This blog discusses about SQL database error 5172 that indicates that the database file header is corrupt. It also provides an insight on causes and solutions of SQL Database file header corruption in MS SQL database. The solutions comprise manual methods and using a SQL recovery tool to recover the database.
Contents
The two biggest drawbacks of a Database Management System are that it is complex and lengthy, thus making the database prone to corruption. Microsoft SQL is undoubtedly the most preferred choice amongst Relational Database Management System Administrators but this factor does not help in overcoming its drawback. Like other RDBMS, SQL Database is also hit with corruption and one of these is SQL database file header corruption.
SQL server refuses to start. The ERRORLOG shows the following error about the Page Audit property:
2017-10-30 15:45:36.36 spid9s Starting up database ?model?.
2017-10-30 15:45:36.37 spid9s Error: 5172, Severity: 16, State: 15.
2017-10-30 15:45:36.37 spid9s The header for file ?C:\Program Files\Microsoft SQL Server\MSSQL12.DAR_P11D\MSSQL\DATA\model.mdf? is not a valid database file header. The PageAudit property is incorrect.
2017-10-30 15:45:36.39 spid9s Error: 945, Severity: 14, State: 2.
2017-10-30 15:45:36.39 spid9s Database ?model? cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details.
2017-10-30 15:45:36.39 spid9s SQL Trace was stopped due to server shutdown. Trace ID = ?1?. This is an informational message only; no user action is required.
This is a case of Microsoft SQL server error 5172. You can encounter similar other errors. Before discussing about solutions to fix the error, let’s first understand the basics of the Database file Header page, its effects, possible causes, and appropriate solutions.
What is Database File Header Page
In SQL Server, data is stored in a unit called Page, numbered sequentially starting with 0 for the first page in the file. The first page is also known as file header page that contains information about the attributes of the file. Each file is identified with a unique File ID number. For SQL Database, Page no. and File no. together determine a page.
Similarly, SQL data file is stored with extension .MDF and .NDF and the disk space logically distributes itself into pages numbering 0 till n. All disk Input-Output related operations are done on pages. In a nutshell, SQL server reads and writes whole data-pages. Find below structure of SQL server data file:
| Page No | Page Identify |
| 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 |
Page Description – Page 0 (Header): First page of the SQL data file and occupies approx 8KB storage space. Stores metadata about that particular SQL Data file. All files have Header as Page 0, which can?t be recovered by CHECKDB. The entire file is restored if there is Damage or corruption in File Header. Check Header contents with DBCC page. A better option is ?DBCC FILEHEADER? also known as purpose command.
DBCC FILEHEADER
When an Administrator executes SQL Database DBCC FILEHEADER command on a particular database, it searches for two parameters – 1) Database name/ID and 2) File ID. The basic Syntax command is:
DBCC FILEHEADER (?DBName?, ?FileId?); GO
DBCC FILEHEADER command returns a tabular output with table contents indicating information about the database including Output, Growth, Binding ID, Status, Sector size and more.
Now that you have an understanding of SQL Database basics, let?s discuss SQL DB File Header Corruption error.
Probable Reasons and Effects ? SQL File Header Corruption
As discussed above, the SQL File Header page is an important component and any corruption on this page directly affects the database to the extent that it is rendered dysfunctional. The outcome is ?Inconsistency in Database? or ?Complete Inaccessibility of Database? in extreme cases. It is a fact that all SQL Administrators keep a Hawk?s eye view of their database administration and remain alert 24×7; despite that unexpected reasons may lead to File Header page corruption:
- Drivers and Controllers behaving badly
- Unpredicted Power Outages
- Abrupt Rebooting of SQL server
Corporates have a backup disaster recovery plan in place but most of the time, it is either unreachable or the Administrator is not available to execute the plan.
Solutions to Recover from Disaster
The best way to recover is backup data restoration and almost all Corporates have a backup of their data, however, in the situation where the backup is also taken at the same location and there is a sudden power outage, and the result might be SQL backup file corruption. In that case, you need to follow the below-mentioned steps:
- 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 (DB) with same DB name and Filenames
- Stop SQL Server
- Overwrite newly created MDF and LDF
Above mentioned steps will lead to database recovery online. Establish an EMERGENCY or SINGLE USER mode for this database and execute DBCC CHECKDB as follows:
DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
Note: Executing DBCC CHECKDB command may have an opposite effect ? you might end-up losing partial data with flags such as REPAIR_ALLOW_DATA_LOSS. Organization?s data is crucial and it should be available as is. A partial data loss may result in business loss.
With this option, you have ruled out a data recovery option using the manual method. The only and in fact the best way is to use Stellar Repair for MS SQL. This SQL database recovery tool repairs corrupt MDF files and restore complete data.
Check the working process of Stellar SQL Recovery Software:
Conclusion
Though RDBMS is used by most SQL Database Administrators, it is prone to corruption and unexpected shutdowns. To overcome this problem, create the backup of the precious database at a different location for easy restoration. If this option is also not available, resort to Plan B and get third party software to deal with SQL Database File Header corruption and recover your corrupted database in no time. In fact, Corporates should have a ready third party software as an alternate backup plan to reduce business loss for want of database availability.
