We recently were forced to migrate SQL Servers because of a failing OS. The failing OS was transpired by failing HDD's becuase of bad sectors. Now that the new server is up and running we are not able to perform a function that allows for us to search archived tickets.

Below is the query that is found within the failing stored procedure:

SELECT (2 * messageID) AS linkID, DateTimeStamp AS DateEntered,
Subject AS Title, '' AS Description,
cast(body as nvarchar(100)) MessageBody
FROM SupportMessagesArchive SM
WHERE TicketNumber = @ticketid


SELECT ((2 * AE.ActionEventID) + 1) AS ActionEventID,
AE.ActionEventDateEntered AS DateEntered,
AE.ActionEventTitle AS Title,
Description = cast(ActionEventDescription as nvarchar(100)),
MessageBody = cast(ActionEventCustomerComments as nvarchar(100))
FROM ActionEventsArchive AE ----WITH (NOLOCK)
LEFT JOIN ServerEventType ON AE.serverEventTypeID = serverEventType.eventTypeID
WHERE AE.ActionID = @ticketid

The results of the query as reported by Query Analyzer is
Server: Msg 3624, Level 20, State 1, Line 1
Connection Broken

If I break the above UNION Query into separate queries, I get the same error.

If I take the first query and do a SELECT TOP 1 the same error is generated, however if I remove the WHERE clause, I get desired result set.

If I take the second query and do a SELECT TOP 1 without the WHERE clause I get the same error. But if I include the where clause I get the below error:

Server: Msg 3624, Level 20, State 1, Line 1

Location: recbase.cpp:1378
Expression: m_offBeginVar < m_SizeRec
SPID: 55
Process ID: 3452

Connection Broken

I looked up the above error and performed a DBCC CHECKDB based on the information found within the URL

DBCC CHECKDB('MY_DB_NAME', REPAIR_REBUILD) returned the below error:

Server: Msg 3624, Level 20, State 1, Line 2

Location: R:\sql\ntdbms\storeng\drs\include\record.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 58
Process ID: 3452

Connection Broken

If I query the sysindexes table for the above table names, I see that there are indexes based upon those tables, but a sp_helpindex states that there are no indexes found on any of the tables.

I am at a loss here as to what to do. Can anyone please provide their suggestions?