Records on a Page? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Records on a Page?

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2007-10-01 : 16:56:29

I was able to fix this data with no data loss.
However How would I use a restored backup to find the records that were on this particular page?

DATE/TIME: 10/1/2007 12:33:36 PM

DESCRIPTION: Error: 823, Severity: 24, State: 2
I/O error (torn page) detected during read at offset 0x00000003aa6000 in file 'K:\Program Files\Microsoft SQL Server\MSSQL\Data\MC_20070101_2.mdf'.

COMMENT: (None)

JOB RUN: (None)

You can do anything at www.zombo.com

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-10-01 : 17:11:24

Restore the backup using a different database name. Take the offset in the error message, convert to decimal and divide by 8192 (number of bytes in a page). Use DBCC PAGE on that page in the restored database and you'll see the records on the page. See my blog post at http://www.sqlskills.com/blogs/paul/2007/10/01/InsideTheStorageEngineUsingDBCCPAGEAndDBCCINDToFindOutIfPageSplitsEverRollBack.aspx for more info on using it.

How were you able to fix the error without data loss? When you ran DBCC CHECKDB, did you find the page is from a non-clustered index?

Paul Randal
Managing Director, SQLskills.com

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2007-10-01 : 17:37:15

Ran Repair_Rebuild and it was successful.

You can do anything at www.zombo.com

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-10-01 : 17:47:35

ok - so it was a non-clustered index page.

You should do root-cause analysis to find out why the torn-page occured in the first place - I'm guessing you had a power outage?

Paul Randal
Managing Director, SQLskills.com

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 17:53:20

Paul:

Won't a power outage cause some sort of rollback [on reboot] that would sort-out a half-written-multi-block-disk-write ?

Thanks,

Kristen

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-10-01 : 18:00:25

No - the page still needs to be read in so the transaction log can be redone or undone. If it's torn in any way then reading the page will detect that.

It could also be the case that the transaction committed and is no loonger in the active portion of the log and a disk/controller error caused the page to be torn without any power outage occuring.

Thanks

Paul Randal
Managing Director, SQLskills.com

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 18:23:18

I'll stop just flicking the power off to demonstrate how resilient SQL Server is then

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2007-10-01 : 19:25:25

Great Guess, we have been having power outages and spikes due to a building renovation next door.

You can do anything at www.zombo.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources