sysobjects corruption

  • UV

    SSC Veteran

    Points: 295

    i get the following error when selecting:

    sysobjects: I/O error (torn page) detected during read at offset 0*0000000000000 in file ‘D:\XXX.mdf’

    tbl_a: Could not continue scan with NOLOCK due to data movement.

    any idea? someone?

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Please run the following and post the full and unedited output

    DBCC CheckDB(<database name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

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

    SSC Veteran

    Points: 295

    Server: Msg 8966, Level 16, State 1, Line 1

    Could not read and latch page (1:1610) with latch type SH. sysobjects failed.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Restore from a clean backup, your database is irreparably corrupt.

    Once done, check your IO subsystem for errors.

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

    SSC Veteran

    Points: 295

    thank you very much!!!!

  • psingla

    Hall of Fame

    Points: 3840

    If it is a very big database then you can restore only page (1:1610) to save restoration time

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • Gail Shaw

    SSC Guru

    Points: 1004446

    psingla (6/26/2013)


    If it is a very big database then you can restore only page (1:1610) to save restoration time

    No, he can't.

    Firstly because it's a SQL 2000 database and page restore didn't exist in SQL 2000, second because you can't do a page restore of the critical system tables, which is where the corruption is in this case.

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

    Hall of Fame

    Points: 3840

    GilaMonster (6/26/2013)


    No, he can't.

    Firstly because it's a SQL 2000 database and page restore didn't exist in SQL 2000, second because you can't do a page restore of the critical system tables, which is where the corruption is in this case.

    Thanks Gail,I was not aware that page restore doesn't work for critical system tables

    but how you came to know this is SQL 2000?I haven't worked on 2000...

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • Gail Shaw

    SSC Guru

    Points: 1004446

    psingla (6/26/2013)


    but how you came to know this is SQL 2000?I haven't worked on 2000...

    Because it's posted in the SQL 2000 forum, because the error messages are in the SQL 2000 form (they were all rewritten for SQL 2005) and because the corruption message references sysobjects, a table that does not exist in SQL 2005+

    It could be SQL 7 too, but it's definitely not SQL 2005 or higher.

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

    Hall of Fame

    Points: 3840

    GilaMonster (6/26/2013)


    corruption message references sysobjects, a table that does not exist in SQL 2005+

    It could be SQL 7 too, but it's definitely not SQL 2005 or higher.

    Thanks again,I missed that point it's posted in the SQL 2000 forum.

    BTW sysobjects view exists in 2005+ versions also.So if somebody is trying to select data from sysobjects in later 2005+ then he can get the same error that is listed here.

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • Gail Shaw

    SSC Guru

    Points: 1004446

    psingla (6/26/2013)


    BTW sysobjects view exists in 2005+ versions also.

    The view does, but it's a view and not a table and since a view does not store data, sysobjects cannot become corrupt. Its underlying tables can, but not the view.

    So if somebody is trying to select data from sysobjects in later 2005+ then he can get the same error that is listed here.

    No he can't.

    He cannot get this exact error for a number of reasons, not least because the errors were rewritten in SQL 2005 and no longer have the form that was posted here. Also because sysobjects is not a table in SQL 2005+, it's a view, and any corruption that affects the underlying tables will be reported as being in the underlying tables, not sysobjects.

    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

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

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