Resolving SQL Server Errors 5171 and 5172
The header page of an MDF file plays a critical role in allowing SQL Server to load, attach, or open a database. When SQL Server fails to recognize the header of the primary data file, it throws various errors. SQL Server errors 5171 and 5172 typically occur due to issues with the primary database file (MDF) or the log database file (LDF).
These errors typically occur when the MDF file's structure or format is incorrect. In this article, we discuss the possible causes and methods to resolve SQL Server errors 5171 and 5172.
What Causes the SQL Server Cannot Open the Database File Error (5171/5172)?
You may encounter these errors in scenarios such as insufficient permissions for the SQL Server service account on the MDF file, or when an incorrect file (for example, an NDF file instead of an MDF file) is selected during database attachment. These errors can also occur due to corruption in the MDF or LDF file.
Common causes of corruption include:
Lack of storage space on the hard drive where the database file is located
Bad sectors on the disk storing the database file
Abrupt system shutdown or unexpected power failure
Bugs or internal issues in SQL Server
Virus or malware infection
Methods to Resolve SQL Server Errors 5171 and 5172
Before proceeding with the methods below, ensure that you have selected the correct MDF file and verify the permissions on the folder where the database files are stored. Also, confirm that the SQL Server service account has complete control permissions. Once permissions are validated, proceed with the troubleshooting methods.
Method 1 – Check and Repair the MDF File
Corruption in the MDF file is one of the primary reasons behind SQL Server errors 5171 and 5172. To verify corruption, you can run the DBCC CHECKDB command, which checks the consistency of database pages, rows, index relationships, and other objects.
DBCC CHECKDB Test11;
If corruption is detected, SQL Server displays error details along with recommended repair options. If you have a recent and valid backup of the database, restoring the .bak file is the safest approach. However, if the backup is unavailable or also corrupted, you can attempt to repair the database using DBCC CHECKDB.
Below is the general syntax of the DBCC CHECKDB command with repair options:
DBCC CHECKDB
[
(
db_name | db_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
)
]
[
WITH
{
[ ALL_ERRORMSGS ]
[ , EXTENDED_LOGICAL_CHECKS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
[ , { PHYSICAL_ONLY | DATA_PURITY } ]
[ , MAXDOP = number_of_processors ]
}
];
To repair severe corruption, you can use the REPAIR_ALLOW_DATA_LOSS option, as shown below:
DBCC CHECKDB (N'testing35', REPAIR_ALLOW_DATA_LOSS)
WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
Although REPAIR_ALLOW_DATA_LOSS can fix highly corrupted MDF files, it may result in data loss and does not always work in every scenario. For large databases, the command may need to be executed multiple times, making the process time-consuming.
To avoid data loss and speed up the recovery process, you may consider using a professional SQL database repair tool such as Stellar Repair for MS SQL. This tool is designed to repair corrupt MDF files while preserving data integrity.
Key Features of Stellar Repair for MS SQL
Repairs both MDF and NDF files without file size limitations
Recovers all database components with high accuracy
Restores deleted records from the MDF file
Allows saving repaired data to a new or live database, as well as formats like XLS, HTML, and CSV
Supports SQL Server 2022, 2019, 2017, and earlier versions
Method 2 – Rebuild the Transaction Log File
SQL Server errors 5171 and 5172 may also occur if the transaction log file (LDF) is corrupted or missing. In such cases, you can rebuild the transaction log by attaching the database without the LDF file. SQL Server automatically creates a new transaction log file in the same directory as the MDF file.
Use the following command to attach the database and rebuild the log file:
CREATE DATABASE testdb
ON
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\testdb5.mdf')
FOR ATTACH_REBUILD_LOG;
GO
Conclusion
Damage to the MDF file, corruption in the header page, or issues with the LDF file can trigger SQL Server errors 5171 and 5172. In this article, we covered practical methods to troubleshoot and resolve these errors. While DBCC CHECKDB can help in certain scenarios, using a professional SQL database repair tool such as Stellar Repair for MS SQL is often the safest and most efficient approach. The tool enables quick repair and recovery of corrupt MDF and NDF files while maintaining complete data integrity.