How to Resolve SQL Server Error 5172 – Fix SQL Server Header Error 5172
SQL Server stores its physical data in the primary database file, which contains the data by pages which store data. The header information of a .mdf file is stored by the first page, called a header page. This page contains the important information of this database file like file size, signature etc…. When a user attempts to attach the MDF / LDF database file stored on their system, a message box appears “The header for the “databasename.mdf” is not a valid database file header. The FILE SIZE property is incorrect. (SQL Server error 5172). It occurs when the header information of a .mdf file becomes corrupted.
Normally when this happens, SQL server refuses to start. The ERRORLOG shows the following error about the Page Audit property:
2021-02-25 05:05:06.06 spid9s Starting up database ‘test’.
2021-02-25 05:05:06.07 spid9s Error: 5172, Severity: 16, State: 15.
2021-02-25 05:05:06.07 spid9s The header for file ‘C:\Program Files\Microsoft SQL Server\MSSQL12.DAR_P11D\MSSQL\DATA\test.mdf’ is not a valid database file header. The PageAudit property is incorrect.
2021-02-25 05:05:06.09 spid9s Error: 945, Severity: 14, State: 2.
2021-02-25 05:05:06.09 spid9s Database ‘test’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details.
2021-02-25 05:05:06.09 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 number and File number and all of them 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.
When an Administrator executes SQL Database DBCC FILEHEADER command on a particular database, it searches for two parameters – 1) Database name and 2) File ID. The basic Syntax command is:
DBCC FILEHEADER (‘DBName’, ‘FileId’);
DBCC FILEHEADER command returns a tabular output with table contents indicating information about the database.
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 always keep an eye 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
- Corrupted or damaged log file or data file.
- If you try to attach a higher version of the SQL database server file to a lower version.
- A sudden power failure that affects the driver and controller.
- Failure to shut down the system or restart SQL Server.
Common error experienced in Microsoft SQL Server error 5172 – The file size property is incorrect and does not know how to resolve it. To fix this error, we have comes with the successful solution here to fix SQL Server header error 5172.
Microsoft SQL Server Error 5172 – The File Size Property Is Incorrect or SQL Server Header Error 5172- Reason For SQL Server Header Error 5172
There can be several reasons for SQL database file header corruption such as:
Now Manual Solution to Fix 5172 Error in SQL Server
To resolve the SQL Server error 5172, you can use the backup of the SQL Server database and restore the data from the backup. But sometimes it happens that the backup may also be in the same location and a sudden power failure occurs, which can damage the SQL backup file. For this, the below steps can be used to fix SQL Server header error 5172:
- Make note of database name and file names of .mdf and .ldf files on the database with error.
- Stop the SQL Server instance by rright click on sql server instance in SSMS or in SQL configuration to stop the instance.
- Copy MDF and LDF files to another location on server or remote file server.
- Delete the original MDF and LDF files which are copied in above step.
- start the SQL Server instance.
- Create a new database (DB) with the same DB name and file name of database with error
- Quit SQL Server Management Studio.
- Lastly, Overwrite newly created MDF and LDF files.
What if the Error Not Fixed After Performing Manual Method?
There is a possibility that the manual process mentioned above does not resolve Microsoft SQL Server error 5172 – The File Size Property Is Incorrect. Mainly if no good backup available. If a good health backup of the SQL Server database is not available then manual method will/may fail where third party tools may be used.