How to deal with SQL database Error 5172

The two biggest drawbacks of Database Management System is 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 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. In the following article, you will get an insight on causes and solution of SQL Database file header corruption in MS SQL database but before that, understand the basics of 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 NoPage Identify
Page 0Header
Page 1First PFS
Page 2First GAM
Page 3First SGAM
Page 4Unused
Page 5Unused
Page 6First DCM
Page 7First BCM

Page Description – Page 0 (Header): First page of the SQL data file and occupies approx 8FB 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 command returns a tabular output with table contents indicating information about the database including Output, Growth, Binding ID, Status, Sector size and more.

Output of DBCC FILEHAEDER

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:

  1. Stop SQL Server instance
  2. Copy MDF and LDF files to another location
  3. Delete original MDF and LDF files
  4. Start SQL Server instance again
  5. Create new database (DB) with same DB name and Filenames
  6. Stop SQL Server
  7. 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:

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.

free download

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.

Comments(9)
  1. Jim K. Milner December 27, 2018
    • Eric Simson December 27, 2018
  2. Madison Latoya September 25, 2018
    • Eric Simson September 26, 2018
  3. Merle Simpson December 14, 2017
  4. Timothy Carson November 28, 2017
    • Eric Simson November 29, 2017
  5. Cary Lynch November 23, 2017
    • Eric Simson November 24, 2017

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.