SQL database page level corruption

DBAPriya

30 pts.

Tags:

SQL

SQL Database

SQL Server

Hello Experts,

We are facing page level corruption in SQL database. The database size is 650 GB. What should we do?

Asked: May 25, 2018 9:53 AM Last updated: September 6, 2018 1:23 PM

Answer Wiki

Last updated: May 28, 2018 3:24 PM GMT

Michael Tidmarsh65,610 pts.
History
Contributors
- Ordered by most recent
- Michael Tidmarsh65,610 pts.
- DBAPriya30 pts.
- ToddN2000119,355 pts.

Thanks. We'll let you know when a new response is added.

Check out this link for a good write up on troubleshooting and a fix.

MSSQLTIPS

Discuss This Question: 2 Replies

There was an error processing your information. Please try again later.

Thanks. We'll let you know when a new response is added.

Send me notifications when members answer or reply to this question.

Subhendu Sen May 25, 2018 11:20 AM GMT

Is there a new hardware inserted in the system? If yes, remove this one. Assuming you have taken the database backup of the original MDF and LDF files. Next try to run DBCC CHECKDB command which helps to fix the corruption issue in SQL database. Moreover it fixes the inconsistency in SQL server database by performing Database Consistency Checks.

124,080 pointsBadges:

report
ToddN2000 Sep 6, 2018 1:23 PM GMT

Also found this write up on our servers help docs

Resolving SQL Database Page Level Corruption

Before starting the process take a full backup of the database files to keep yourself on a safer side if anything goes wrong.
Then follow the instructions below:

Check the difference between an original and the corrupted file using a text comparison software.
Execute DBCC CHECKDB command on the damaged file. This command performs a systematic data integrity check and identifies the issues.
Now, execute DBCC PAGE command after switching on the trace flag 3604 (for this run DBCC TRACEON command). It will examine infected page content.
DBCC TRACEON (3604)

DBCC PAGE ({ ‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3}])

Here, filenum and pagenum are page ids.

Other parameters are defined below:

0 - the header part of the page will be printed

1 - SQL page header with per row hex dumps

2 - Detailed page header with a full hex dump of the page

3 - Description of per row interpretation of page header

Now, run the command ‘SELECT * from dbo.tablename’. This command is for confirming the page number and.
Now, you know the location where corruption has taken place. Use the text comparison software to compare it with original page.
Finally, fix the corrupted page after detecting flaws from the previous step and then, execute the DBCC CHECKDB command on the restored file.

119,355 pointsBadges:

report