February 14, 2008 at 7:33 am
I am running SQL Server 2000 SP 4 on a Windows Server 2003 environment.
I have a table 'ASSIGN_ID' in my database that contains 2 columns.
Table_Name varchar(30)
UNIQUE_ID numeric 9(15,0)
Something is happening so that the following select fails:
SELECT UNIQUE_ID FROM ASSIGN_ID WHERE TABLE_NAME = 'POLICY'
TABLE_NAME = 'POLICY' is a standard value in the table and it will work without issue for a time. Then the select fails. Viewing the data in the table the row containing POLICY is still there, executing
SELECT UNIQUE_ID FROM ASSIGN_ID WHERE TABLE_NAME LIKE 'POLICY'
returns the row desired.
Performing a
dbcc checktable(ASSIGN_ID) returns the following.
Server: Msg 2511, Level 16, State 2, Line 1
Table error: Object ID 786101841, Index ID 0. Keys out of order on page (1:554), slots 10 and 11.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 786101841, Index ID 0. Keys out of order on page (1:554), slots 40 and 41.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 786101841, Index ID 0. Keys out of order on page (1:554), slots 51 and 52.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 786101841, Index ID 0. Keys out of order on page (1:554), slots 78 and 79.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 786101841, Index ID 0. Keys out of order on page (1:554), slots 80 and 81.
DBCC results for 'ASSIGN_ID'.
There are 90 rows in 1 pages for object 'ASSIGN_ID'.
CHECKTABLE found 0 allocation errors and 5 consistency errors in table 'ASSIGN_ID' (object ID 786101841).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (PS.dbo.ASSIGN_ID ).
Performing a
Dbcc dbreindex (ASSIGN_ID)
Fixes the issue but it has reoccurred.
I've never run into this combination of issues before, can anyone point me in a good direction to start resolving this?
Thanks
Cory Blythe
February 14, 2008 at 8:03 am
If you haven't already looked at the links below, take a look at these two. SQL Server 2000 had issues in SP3. SP4 supposedly fixed these issues, but it sounds like you may still be suffering through a similar problem if not the exact one already reported.
http://support.Microsoft.com/kb/822747
http://support.Microsoft.com/kb/929440
You may have success using sp_fixindex. The sp_fixindex stored procedure uses the undocumented DBCC REPAIRINDEX command.
HOW TO: Use the sp_fixindex Stored Procedure
http://support.microsoft.com/kb/106122/en-us
Q
Please take a number. Now serving emergency 1,203,894
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply