How to Resolve Error 8909 in MS SQL Server Database?

When working with the MS SQL Server, you often face various issues and errors. One such error is error 8909. The complete error message is displayed as:

Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:72792) contains an incorrect page ID in its page header.

This error is usually reported by the DBCC CHECKDB command when the allocation integrity checks on database pages are failed. It happens due to incorrect Page ID, issues with the GAM, SGAM, or PFS pages, or corruption in tables. Sometimes, this error message is also returned by RAISERROR (Transact-SQL) statement.

Below, we will look into the causes behind the error 8909 in SQL server and discuss the solutions to fix this error.

Causes of Error 8909 in MS SQL Server Database

The SQL error 8909 is associated with page-level issues in database files or corruption in the database tables. Corruption in SQL database tables can occur due to various factors, such as:

• Power outages or sudden shutdown of the SQL Server while working on the database
• Bugs in SQL Server
• Hardware failure or software issues
• Malware infection in the system hosting the database
• Improper termination of system
• Forcefully closing the MS SQL application

Methods to Resolve the Error 8909 in MS SQL Server Database

Here are some methods you can follow to resolve the error 8909 in MS SQL Server database.

Method 1: Restore Database from Backup

If there are issues with the pages in database or corruption in tables, then the best option is to restore the database from the backup. Before restoring the database from backup, verify whether your backup file is readable or not. This helps prevent backup restore errors at the time of restoring the backup. To check the backup file, you can run the VERIFYONLY (Transact-SQL) statement. If this statement returns a success message, then proceed to restore the backup file. For this, use the below command:

This command will restore the entire database from backup.

Method 2: Use Restore Page Method

The error 8909 indicates corruption in pages in the SQL database file. In such a case, you can restore specific corrupted pages from the database. For this, you can perform ‘Page Restore’ by using the T-SQL command or via SQL Server Management Studio (SSMS).

To restore pages using the T-SQL, run the below command:

Note: You need the file ID of the database file, along with the page and page ID. You can get this information in the error message.

The ‘Page Restore’ method can restore only pages of the database. It works on database, which is created using the full or bulk-logged recovery mode. It cannot restore Page 0 of the file boot page, database boot page, full-text catalog, and transaction log.

Method 3: Use DBCC CHECKDB Command

If the backup is not readable or available, then you can try to repair the SQL Server database by using DBCC CHECKDB command. You can use the REPAIR_ALLOW_DATA_LOSS option with the command.

Note: Before using the command, first make sure you have the admin rights and ALTER permission on the database.

First, change the database to single user mode using the below steps:

This will prevent other users from making any changes in the database during the repair process.

Next, run the DBCC CHECKDB command as given below:

Once the repairing process is finished, change the database mode from single-user to multi-user by using the below command:

Note: Using the REPAIR_ALLOW_DATA_LOSS repair option may deallocate the pages and rows in the table that are not recoverable, resulting in data loss. It does not guarantee complete data recovery.

Method 4: Use an Advanced SQL Repair Tool

If you don’t have a healthy recent backup or need to repair the SQL database without any data loss, then use the advanced SQL repair tool, Stellar Repair for MS SQL. The SQL repair tool is designed to repair corrupt SQL database (both MDF and NDF) files with complete integrity and precision. It can recover all the objects, including pages, tables, and procedures, from the database file.

The tool allows you to save all the data recovered from the database to a new SQL database file and various other formats. It can help you resolve corruption-related errors in SQL databases, including the error 8909.

Highlighting Features of Stellar Repair for MS SQL

• Repairs corrupt both MDF and NDF files with precision.
• Recovers all the objects from the database file, including tables, indexes, and even deleted records.
• Allows saving the recovered data in a new database file or an existing database file (offline) and in other formats, like Excel, CSV, HTML, etc.
• Offers a Search feature to find and recover specific objects.
• Provides a preview of the recovered data before saving.
• Supports MS SQL version 2022, 2019, 2017, 2016, and earlier.

To Conclude

You can resolve the error 8909 in the MS SQL Server database using the above-mentioned methods. You can restore the affected database from the backup or use the DBCC CHECKDB command to repair the database. However, if you need a faster and more reliable way to fix the error 8909, use SQL repair tool such as Stellar Repair for MS SQL. This powerful tool can extract all the data from the database with corrupt pages and save it in a new database file, thus helps resolve the error. It supports both MDF and NDF files.