Post reply

Error in table 'sys.sysbinobjs' on master database

  • March 22, 2011 at 1:35 pm

    #237145

    Hi,

    We have a SQL server DatabaseIntegrityCheck job to check the SYSTEM_DATABASES, and it shows an error message below in the logs now:

    Msg 2570, Level 16, State 2, Line 1

    Page (1:354), slot 28 in object ID 58, index ID 1, partition ID 281474980511744, alloc unit ID 281474980511744 (type "In-row data"). Column "name" value is out of range for data type "nvarchar". Update column to a legal value.

    CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysbinobjs' (object ID 58).

    CHECKDB found 0 allocation errors and 1 consistency errors in database 'master'.

    However, when run the command "DBCC CHECKDB ([master]) ", it returns no error. Don't know what a problem is. Could someone please let me know what we should do? Any fix need to be done?

    Thanks in advance,

    Marie

  • March 22, 2011 at 2:03 pm

    #1302160

    Not sure why it's appearing and disappearing, have asked for a second opinion on that.

    Got a clean backup of master?

    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

  • March 22, 2011 at 2:44 pm

    #1302183

    Thanks, Gail!

    Unfortunatly, the backup was taken after the problem occured.

  • March 22, 2011 at 2:59 pm

    #1302192

    And you don't have older backups?

    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

  • March 22, 2011 at 3:14 pm

    #1302197

    We have just built the system and implemented more than 50 user databases recently. The old backup may not useful.

  • August 13, 2013 at 8:38 am

    #1640967

    I would imagine the difference is that the dbcc checkdb parameters are different. If you're running Ola Hallengren's scripts, he uses DATA_PURITY.

    From BOL:

    DATA_PURITY Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.

    For databases created in SQL Server 2005 and later, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB checks column-value integrity by default. For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.

    If PHYSICAL_ONLY is specified, column-integrity checks are not performed.

    I do not believe it's uncorrectable corruption. http://support.microsoft.com/kb/923247 shows how to fix the error.

    Chrissy
    Founder, dbatools.io

  • August 13, 2013 at 9:09 am

    #1640986

    ctrlb (8/13/2013)

    I do not believe it's uncorrectable corruption. http://support.microsoft.com/kb/923247 shows how to fix the error.

    That support article explains how to fix the error in user tables. The table in question here is a system table and cannot be updated.

    p.s. Two and a half year old thread.

    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

  • August 13, 2013 at 9:32 am

    #1641005

    You're right -- I actually found that after and forgot to come back.

    I was aware that it's an old thread, but it's placing highly on Google, which is how I happened here a few years later.

    I'm still contemplating a solution for my own corruption. It's on a busy clustered server, unfortunately. This also seems to be a common problem; I wonder why.

    Chrissy
    Founder, dbatools.io

  • August 13, 2013 at 9:38 am

    #1641011

    If you have this problem, open a case with CSS. Data purity errors should not ever happen in the system tables.

    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 9 posts - 1 through 8 (of 8 total)

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