Summary: This post talks about how to diagnose and repair critical database corruption in SQL Server with GAM, SGAM, and IAM pages. Learn about common errors, gain insights into diagnostics, repairs, and prevention strategies, and solutions like database restoration or third-party repair software. Also, get tips to prevent future corruption.
In SQL Server, a page is the fundamental unit for storing data and extents are the basic unit that help in efficiently managing the pages. An extent is a combination of eight physically connecting pages. In SQL Server, there are two types of allocation maps used for recording the extent of allocation – Global Allocation MAP (GAM) and Shared Global Allocation MAP (SGAM). There is another page, called Index Allocation Map (IAM) that maps the extents in the 4-GB part of the SQL Server database file utilized by an allocation unit. Sometimes, these SQL database pages get corrupted or damaged due to various reasons, requiring repair of database corruption. In this post, we will see how to diagnose and repair SQL database corruption – GAM, SGAM, and IAM page corruption issues in SQL Server.
What are the GAM, SGAM, and IAM Pages?
Global Allocation Pages (GAM) are responsible for handling allocation extents, inside the database. They help in identifying free extents when required.
Shared Allocation Map (SGAM) pages are like granular maps. They handle the allocation of extents with specific file groups in the database.
Index Allocation Map (IAM) pages are special internal pages that act like a registry for data storage. They track the data page’s location in the database file.
How to Diagnose GAM, SGAM, and IAM Page Errors?
When you run queries, you may face an error message similar to this one:
Msg 8905, Level 16, State 1, Line 1
Extent (5678:1234) in database ID 10 is marked allocated in the GAM, but no SGAM or IAM has allocated it.This error indicates that the GAM page is corrupt.
Another way to detect errors in database pages is by using the DBCC CHECKDB command (see the below example).
DBCC CHECKDB (stellardb)
GOThere is an undocumented command, named dbcc PAGE. This is an internal command used by Microsoft. However, you can use it to diagnose the database pages. Here’s the code to use this command.
DBCC PAGE (stellardb, 1, 354, WITH NOHEADER)
GOHere, stellardb is the database name, 1 is the file number, and 354 is the page number.
How to Fix Corruption in SQL Database GAM, SGAM, and IAM Pages?
Here are some solutions to fix the GAM, SGAM, and IAM page errors, and corruption in the SQL database.
Restore Database from Backup
If you have a backup, you can use it to restore your data. Here are the steps to restore database backup using SQL Server Management Studio (SSMS):
- In SQL Server Management Studio (SSMS), go to Object Explorer and right-click on Databases.
- Then, select the Restore Database option.
- Select the Device option on the General page and then press the Browse button to select the backup and press OK.
Use DBCC CHECKDB Command
To fix the GAM, SGAM, and IAM page errors, and database corruption, you can use the DBCC CHECKDB command in SQL Server. Here’s the code to repair the database using the DBCC CHECKDB command:
USE master;
GO
ALTER DATABASE stellar
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB('stellar',REPAIR_REBUILD)
GO
ALTER DATABASE stellar
SET MULTI_USER;
GOThe above code will go to the master system database, set the database in single-user mode, repair the database, and bring it back to multi-user mode.
Use a Third-Party SQL Database Repair Software
If the DBCC CHECKDB command fails to repair the database, you can use third-party SQL repair software, like Stellar Repair for MS SQL. This software can easily repair SQL Server database (mdf and ndf) files. It can recover all the data from corrupted database files and save it in a new database file or various other formats.
To repair the database using Stellar Repair for MS SQL, follow these steps:
Note: Take the database offline before initialing the repair process.
- Download the software from the official website.
- Install the software and launch it.
- Click Browse to select the database file (You can use the Find button if you do not know where the data file is stored).
- After selecting the data file, press the Repair button.
- Once repaired, you can save the data in a new database or other formats, like CSV, HTML, and Excel.
Best Practices to Prevent GAM, SGAM, and IAM Pages Corruption
Here are some useful tips you can follow to prevent corruption in SQL Server database pages:
- Check your hardware, especially your hard drives, to make sure that they are fine. Replace the disks if they’re old or throwing errors. If possible, create a mirror RAID for your SQL Server data.
- Avoid viruses and malware attacks. Make sure to install Antivirus and Antispam software.
- Secure your network with the best firewalls and network segmentation, and encrypt your data. Create secure VPN networks, if applicable.
- Make sure to update your operating system and software with the latest updates.
Conclusion
If the GAM, SGAM, or IAM pages in the database are corrupted, the easiest way is to restore the database from the last backup. If the backup is obsolete or not working, you can try to repair the database using the DBCC CHECKDB command. In some situations, the DBCC CHECKDB command does not work. In this case, you can use Stellar Repair for MS SQL. This software can easily repair the corrupt database and recover all the objects with complete integrity.
