Post reply

Torn page

  • 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 ?

  • June 22, 2010 at 2:07 am

    #1183130

    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://qa.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

  • June 22, 2010 at 2:47 am

    #1183144

    Its academic only.

  • June 22, 2010 at 2:53 am

    #1183151

    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

  • July 13, 2010 at 4:31 am

    #1191883

    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.

  • July 13, 2010 at 4:37 am

    #1191886

    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

  • July 13, 2010 at 8:19 am

    #1192050

    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.

  • July 13, 2010 at 8:21 am

    #1192054

    sorry guys...

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

  • July 13, 2010 at 8:41 am

    #1192072

    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

  • July 13, 2010 at 1:00 pm

    #1192299

    pages deallocated by dbcc repair command

  • July 13, 2010 at 1:13 pm

    #1192307

    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

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

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