5

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.

  • 1
    The problem is not your select statement. The problem is corrupt data in the DB. Do you have a good, clean, backup? – CaM Jul 10 '18 at 14:15
  • @CaM Unfortunately no, but data in Log table is not important, I can afford to lose it to solve the problem. – Tomas Jul 10 '18 at 14:32
  • Have you tried to move the data into a new table ( SELECT * INTO newLog FROM Log;)? what happens when you query the same without TOP(10)? – AMG Jul 10 '18 at 14:41
11

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.

  • 2
    @Tomas - From your latest comment (the data in the log is not critical), I'd do DBCC CHECKTABLE ('dbo.Log', REPAIR_REBUILD), and follow up with REPAIR_ALLOW_DATA_LOSS if needed. – RDFozz Jul 10 '18 at 14:42
  • 1
    Regardless of which path you take, you should seriously consider running DBCC CHECKDB to confirm nothing else is messed up. And you probably should set up a job that runs this at least once a week, along with scheduled backups, etc. – CaM Jul 10 '18 at 14:55
  • You might also want to perform a CHKDSK <DRIVE:> on the Windows drives to verify that the corruption is not on the hardware. – hot2use Jul 11 '18 at 7:59
  • The answer provided by @RDFozz is on point and should be epitome of a perfect disaster recovery or data corruption strategy. SQL server do provide some level of native solutions to help fix/repair corruptions but in some cases is unsuccessful. There are robust third party data recovery/repair tools that provide help in such worst case scenarios - check out stellarinfo.com/sql-recovery.php – samosql Jul 12 '18 at 14:40
  • 1
    REPAIR_REBUILD works, but note that CI page 1:5042 points to CI page 1:5044, but 1:5044 thinks it is being pointed to by some other CI page (which could be 0:0, an allocated page, or a deallocated page). Forcing use of various non-CIs via hints, with or without SELECT TOP 100% ORDER BY DESC as views, could BCP OUT that other page (along with good or not so good pages). In which case BCPs IN to a new table with IGNORE_DUP_KEY (if possible) declared can preserve data from that "some other CI page", along with other possibly broken doubly-linked pages, for future forensic work. – Bill Feb 2 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.