SQL Server – How to Repair Corruption for ‘Possible Chain Linkage Problem’

corruptionsql serversql-server-2008

I have large table with 15 000 000 record and suddenly Select Top stopped working. I use MS SQL Management Studio.

This is not working, the query return 0 records:

SELECT TOP (10) 
       [Id]
      ,[Result]
      ,[DateStamp]
      ,[ConversionTime]
      ,[Converter]
      ,[SourceFileFormat]
      ,[DestinationFileFormat]
      ,[Ip]
      ,[Source]
      ,[Error]
      ,[UserId]
      ,[TokenId]
      ,[ConversionCost]
  FROM [ca-v2].[dbo].[Log]

To my surprise if I try to select one ID field it works percently.

Working fine:

SELECT TOP (10) 
       [Id]        
  FROM [ca-v2].[dbo].[Log]

Also if I add order by at the end the query starts working too:

SELECT TOP (10) 
       [Id]
      ,[Result]
      ,[DateStamp]
      ,[ConversionTime]
      ,[Converter]
      ,[SourceFileFormat]
      ,[DestinationFileFormat]
      ,[Ip]
      ,[Source]
      ,[Error]
      ,[UserId]
      ,[TokenId]
      ,[ConversionCost]
  FROM [ca-v2].[dbo].[Log]
  Order By [DateStamp]

I ran DBCC CHECKTABLE on the table, and received the following error:

Error: Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 1029578706, index ID 1, partition ID 72057594043367424, alloc unit ID 72057594045071360 (type In-row data). Page (1:5044) is missing a reference from previous page (1:5042). Possible chain linkage problem.

What can I do about this?

With the help of @RDFozz I was able to repair damaged index using sql

ALTER DATABASE "db" SET SINGLE_USER WITH ROLLBACK IMMEDIATE

go

DBCC CheckTable ("log", REPAIR_REBUILD) 

go

ALTER DATABASE "db" SET MULTI_USER WITH ROLLBACK IMMEDIATE

Two important thing here: 1) a database must be set to single user mode. 2) if database is in production and has other connections the SET user command must got with ROLLBACK IMMEDIATE to drop current connections.

Best Answer

When facing database corruption, you will need to determine the extent of the problem. Do you have one corrupt table/index, or many? You will probably want to run DBCC CHECKDB on the database.

Depending on what you find, you have the following options (in decreasing order of safety):

  1. Restore from a backup

    This is usually the simplest and safest solution. However, if you're not certain how long ago the corruption appeared, this may be impractical - it's possible that you have no recent backups without the corruption. And, depending on the usage of the database, throwing away a day's worth of data to fix the corruption might be severely costly, in terms of continuing to be able to run your business.

    However, if the corruption is sever, this may be the only solution you have available. Even if you try some of the following options, you may need to come back to this one.

    If you have backups, keep them handy, and don't cycle any out (if possible) until you've resolved the problem.

  2. Use DBCC CHECKTABLE or DBCC CHECKDB with a repair option

    Your run of DBCC CHECKTABLE and/or DBCC CHECKDB may have indicated a repair option you could use to try to restore things to normal.

    The commands have the same repair options:

    • REPAIR_FAST - as it says, it tried some simple things, and will take less time than the other repair options. As you might guess, this also means it's the least likely to be successfully.

    • REPAIR_REBUILD - This does what REPAIR_FAST does, but is also willing to try rebuilding indexes from scratch - of the problem is in a non-clustered index, this should resolve it.

    • REPAIR_ALLOW_DATA_LOSS - As indicate, it will try to restore the affected objects to usability, but may have to eliminate partial rows and such to do so; you may lose some of your data. This is one of the reasons why restoring a backup is often the best solution.

  3. Frankensteining your bad table (so to speak)

    If only this table is affected, you can try to:

    • Locate a backup where the table is not corrupt (and stand up the backup, under a different name);
    • Drop the bad table;
    • Confirm that this hasn't moved your problem elsewhere by running DBCC CHECKDB again; and
    • Copy in the table and its data from the backup.


    Or, to try to retain as much newer data as possible:

    • Get a backup ready, as above
    • Use REPAIR_ALLOW_DATA_LOSS on the table.
    • If the table is now working, locate rows in the backup that aren't in the repaired table, and copy them in.

As noted by CaM in the comments, you should use this as motivation to make sure you're performing regular maintenance tasks on your database.

First, if you aren't today, start backing it up regularly. Most of the databases I administer have a weekly full backup, and daily differential backups, so we should never lose more than a day's data.

In addition, you should run DBCC CHECKDB on a regular basis as well, to catch corruption before it becomes too serious to fix. This is also something that I try to run weekly.

Related Question