Sysindexes table Cannot Repair

  • yes i agree... when we just run dbcc checkdb ' ' we get errors...

    if we run the entire script (mentioned in my previous post) it fixed it sometimes...

    We fix most of our common dbcc issues by running (checkpoint..... )

    Hope this helps...

    I am a newbie

  • bmannar (7/31/2009)

    if we run the entire script (mentioned in my previous post) it fixed it sometimes...

    We fix most of our common dbcc issues by running (checkpoint..... )

    In that case you have possibly got faulty memory on that server that's resulting in corruptions appearing in memory and not on disk. I would suggest you take a look a the memory controller and memory. Do some diagnostics, check the event logs.

    You should not ever be having 'common' checkDB 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
  • As Paul Randal said these are broken links to statsistics blobs in sysindexes. Unfortunately you cannot delete these with DROP STATISTICS neither with DELETE FROM sysindexes as these will bomb out with the same error.

    Such broken stats result in Error: 7105, Severity: 22, State: 6\nPage (1:8439), slot 3 for text, ntext, or image node does not exist.. when SELECT-ing the column with broken statistics because the engine is trying to access these stats (it's accessing stats for all selected column and NOLOCK does not help).

    What I did to prevent the engine from using the broken stats (and be able to export the data from my user tables) was to update sysindexes.keys column (for all stats) with a bogus (but valid) binary blob like this

    UPDATE sysindexes
    SET keys = 0x3800380004000A00000000000000000001000100000000000400010000000000AD01AD0008000000000000000000000000000500000000020000050000000100
    WHERE (status & 64) <> 0

    The blob value came from keys value of a statistics on a new table with a single column like CREATE TABLE aaa(ID INT) and then created stats on aaa.ID

    On MSSQL 2000 all of the updates had to be done under

    EXEC sp_configure 'allow updates', 1

    . . . with the MSSQLSERVER service stopped and manually started C:\MSSQL\Binn\sqlservr.exe -m as a console application.

    Another unfortunate fact was that all of this had to be done under MSSQL 2000 because the corrupted DB cannot be upgraded to newer SQL Server version -- the attach/restore failed while accessing the broken statistics. . . Yikes!



Viewing 3 posts - 16 through 17 (of 17 total)

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