How to Fix SQL Server Database Error 829?

The SQL Server error 829 occurs when you try to automatically repair a page in the primary/principal database, but it fails for a reason. Automatic page repair is performed only when the status of the database is synchronized and the primary/principal database is sending log records for the database to the secondary/mirror database.

The complete error message appears as follows:

Msg 829, Level 16, State 1, Line 1:
A severe error occurred during database page initialization. The page will be marked as ‘restoring’ and will not be available for use until it is restored from a backup.

As indicated from the message, it is a severe error (Level 16). Message 829 is the error ID and State 1 means that the error occurred during the SQL Server page initialization. Line 1 is the line where the error occurs.

What are reasons for the SQL Server Database Error 829?

There are several reasons that can lead to this error. Some common ones are:

• Hardware Issues. For example, hard drive failure or bad sectors on hard drive can lead to corruption in the database, resulting in the error.
• Power failure. Abrupt failure of power can damage the database page.
• Software issues. Conflicting, faulty, or outdated software programs can also corrupt the database.
• Virus attack. A virus can damage the database file.
• Human errors. Operations such as an incorrect update can corrupt the pages.
• During database restoration, if the database is in the mirroring process or there is a lot of concurrent activity.

Methods to Fix SQL Server Database Error 829

As the main reason for the error 829 is inconsistencies or corruption in the database, you can restore the database from backup or repair the database file using the DBCC CHECKDB command. Let’s discuss these ways in detail.

Restore Database from Backup

In case of any issues with the database, the first and foremost thing to do is to restore the SQL database from backup. If you’ve a recent healthy backup, then follow the below steps to restore the database:

• Open the SQL Server Management Studio (SSMS) and connect to SQL Server instance.
• In the Object Explorer, right-click the Databases node, and select the Restore Database option.

• Select the Device radio button and browse to the location of your backup file
• Select the backup file and then press OK to restore the database.

Repair Database using the DBCC CHECKDB Command

The DBCC CHECKDB command is used to repair corrupt SQL Server databases. If you don’t have a recent healthy backup, you can use the DBCC CHECKDB command to repair the corrupt database.

You need to set the database to single user mode before proceeding to repair it. To do so, right-click your database, select Properties, and select the Options page. In the Options page, select the SINGLE_USER mode and press OK.

Once the database is in single user mode, run the following T-SQL command.

DBCC CHECKDB (‘stellardb’, REPAIR_REBUILD)

GO

If this command fails, you can try to repair the database by using the below command.

DBCC CHECKDB (‘stellardb’, REPAIR_ALLOW_DATA_LOSS)

GO

Note: This command may result in data loss.

Alternate Solution – Repair SQL Database using Stellar Repair for MS SQL

If you do not have a backup or the DBCC CHECKDB command fails to repair the database, you can repair it using a third-party SQL database repair software, such as Stellar Repair for MS SQL. This amazing software can easily repair the database and recover all its objects. Thus, helps fix the 829 error. Here’s how to use the software:

Note: Before proceeding, you need to take your database offline.

• Download the software and install it.
• Click the Browse button to select the SQL Server data file you want to repair. If you don’t know the file location, press the Find button to find the file.

• Then, press the Repair button.

• Once the database is repaired, press the Save icon. You can save the repaired database in a New Database, in an existing database (Live Database), or in other formats, like Excel or CSV.

Conclusion

The error 829 usually occurs when you try to repair a page, but it fails as it is corrupted or in use by another process (restoration, synchronization, mirroring, or upgrade). If the problem is corruption, you can restore the database from backup or repair the database using the DBCC CHECKDB command. If none of these methods works, you can use Stellar Repair for MS SQL to repair the database without any data loss.

Was this article helpful?

YES0

NO

About The Author

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer, and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. Read more