Torn page

Question

Torn page

beejug1983

SSCertifiable

Points: 6491
More actions
June 22, 2010 at 2:00 am

#220330

How can I resolve the page torn issue in my db sql server 2005 ? Can I resolve this using my existing backup ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Is this an academic question, or do you have town pages in your DB?

If it's an academic question, options are restore from a clean backup or run checkDB with the appropriate repair level (potentially losing data)

If you have actual torn pages, please run the following and post the full and complete output.

DBCC CheckDB (<Database Name>) WITH No_INFOMSGS, ALL_ERRORMSGS

This may be worth a read - http://www.sqlservercentral.com/articles/Corruption/65804/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

beejug1983 SSCertifiable Points: 6491 More actions · Answer 2

beejug1983

SSCertifiable

Points: 6491

June 22, 2010 at 2:47 am

#1183144

Its academic only.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Then check out the article I linked to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

azad maratha Default port Points: 1468 More actions · Answer 4

when it runs dbcc checkdb with repair option.it mainly de-allocates pages which are corrupt.and when u again run dbcc u will not get any error.so de-allocated pages remain in the data files.what r the disadvantages of such pages.

if something is wrong in the above part.please clarify.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

There are no disadvantages to that situation - which is why I wrote DBCC CHECKDB that way. There's no alternative to having the deallocated pages be part of the data file - SQL Server can't perform remapping of parts of the data file.

The underlying problem is the (nearly always) IO subsystem that caused the corruption in the first place.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

azad maratha Default port Points: 1468 More actions · Answer 6

so sql server before allocating data pages checks what all things to avoid corrupt pages.

or does it maintain a record.

and if there is some data on that deallocated page.

is there any way to recover data.

i read somewhere that if there is a nonclustered index involve u can do it as it stores data separately.

and can we get a list of deallocated pages in particular database.

and can we run dbcc page command on such pages.

azad maratha Default port Points: 1468 More actions · Answer 7

sorry guys...

a lot of questions r going through my mind.and i m depending on u

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

azadsale (7/13/2010)
so sql server before allocating data pages checks what all things to avoid corrupt pages.

No. When SQL allocates a page, it overwrites what was on it before. Hence anything that made the page 'corrupt' before is replaced.

and if there is some data on that deallocated page.
is there any way to recover data.

You can dump the page as a raw 8k block of binary data, it would be up to you to interpret it correctly. Very much not a trivial operation.

and can we get a list of deallocated pages in particular database.
and can we run dbcc page command on such pages.

You can run DBCC page. Since the page isn't allocated, you'd have to dump it as a raw 8k chunk of data. See earlier comment.

By list of deallocated pages, do you mean all pages not allocated to a table/index? Or pages deallocated by a checkDB repair?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

azad maratha Default port Points: 1468 More actions · Answer 9

azad maratha

Default port

Points: 1468

July 13, 2010 at 1:00 pm

#1192299

pages deallocated by dbcc repair command

Paul Randal One Orange Chip Points: 29544 More actions · Answer 10

The only place to get that from is in the output from repair - it tells you which pages are being deallocated.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005