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.