SQL Server Page Level Corruption

What is Page Level

Database information is stored in the form of page. SQL server database file have mdf and ldf extensions. SQL server contain its all data in primary file, which known as MDF file and LDF file, it contains all Primary data and information of transaction inside of its equal sized page.

Basically MDF file page contains Three of these section.

1. Header section, 2. Body section 3. Slot array

Reason to occur page level corruption

Database page level corruption due to hardware malfunction or hardware failure.

Database corruption page level due to unexpected shutdown of SQL Server Database.

Page level corruption due to SQL database or Machine internal issues in the SQL Server, .

Database corruption in SQL Server can be due to virus intrusion also.

SQL server page level corruption can damage the database contents and make them inaccessible.

Repair page level corruption using

DBCC checkdb - is command that fix the corruption issues in sql database and it fixes the inconsistency in sql server database by performing consistency checks.

It is a combination of DBCC CHECKCATALOG, CHECKALLOC, and DBCCCHECKTABLE.

There are three option to recover the data

1. Repair_allow_data_lose
2. Repair_fast
3. Repair_rebuild

Note - Repair_allow_data_lose is mostly risky command to use. this option only use last because recover either whole data or loss all data.

Execute the following query to perform the operation

#EXEC SP_RESETSTATUS 'DATABASE NAME'

#ALTER DATABASE DB_NAME SET EMERGENCY

#DBCC CHECKDB('DB_NAME')

#ALTER DATABASE DB_NAME SET SINGLE_USER MODE WITH ROLLBACK IMMEDIATE, DBCC CHECKDB('DB_NAME', REPAIR_ALLOW_DATA_LOSS)

#ALTER DATABASE DB_NAME SET MULTI_USER

Conclusion:

Page level corruption can be a major issues for DBA's and there are several reasons to occur that. Manual approach can be useful to repair SQL Server page level corruption but ultimately, use of a third party, automated tool for this purpose will be helpful.

For more references

https://www.mssqltips.com/sqlservertip/2871/troubleshooting-and-fixing-sql-server-page-level-corruption/

http://rameshbabu.in/disaster-recovery/sql-server-database-corruption-causes-and-solution/