How to resolve SQL Server 2005 Index Corrupt issue

Hi All,

I recently came across the following errors on trying to do an integrity check on one of my DBs

Msg 8951, Level 16, State 1, Line 1
Table error: table ‘LIVE.CBNHEA’ (ID 1652916960). Data row does not have a matching index row in the index ‘CBNHEA_ROWID’ (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:3726817:1) identified by (HEAP RID = (1:3726817:1)) with index values ‘ROWID = 5679187 and HEAP RID = (1:3726817:1)’.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘LIVE.CBNHEA’ (ID 1652916960). Data row does not have a matching index row in the index ‘CBNHEA_CBH0’ (ID 3). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:3726817:1) identified by (HEAP RID = (1:3726817:1)) with index values ‘STOFCY_0 = ‘HLT’ and ITMREF_0 = ‘ and HEAP RID = (1:3726817:1)’.
Msg 8951, Level 16, State 1, Line 1
Table error: table ‘LIVE.CBNHEA’ (ID 1652916960). Data row does not have a matching index row in the index ‘CBNHEA_CBH1’ (ID 4). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:3726817:1) identified by (HEAP RID = (1:3726817:1)) with index values ‘STOFCY_0 = ‘HLT’ and LLC_0 = 99 and ITMREF_0 = ‘ and HEAP RID = (1:3726817:1)’.
CHECKDB found 0 allocation errors and 3 consistency errors in table ‘LIVE.CBNHEA’ (object ID 1652916960).
CHECKDB found 0 allocation errors and 3 consistency errors in database ‘xxxxx’.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (xxxxx).

Then I ran the dbcc CHECKDB with ‘repair_rebuild’ on single user mode and it fixed the issue straight away.

This is what I did;

1. Put the DB on single user mode – In order to do this, right-click on the DB and select properties and then go to options, in the options select “Restrict Access” and then select “Single_User” and click “Yes”. You will have to close the management studio and reconnect again before proceeding to the next step.

2. After login on to management studio open up a query window that points to the relevant DB. Type the following

dbcc CHECKDB(‘dbname’,‘repair_rebuild’)

3. run the dbcc CHECKDB(‘dbname’)

This time all the inconsistency errors are gone 🙂

Happy Coding 🙂

Advertisements

How to compare time values in SQL Server 2005

The following query filters the results set based on the time value only

Select * From <Table Name>

Where  Convert(Varchar(8),<DateTime Field Name>,8) >= ‘<Time Value>’

In the above query it will compare a time value with the format of,

           hh:mm:ss

If you don’t want the seconds portion of it simply reduce the length of the converted field

ie. Convert(varchar(5),<DateTime Field Name>,8)      this will only return a time value with the format of,

        hh:mm

Happy coding!!

How to get the number of days in the month (SQLServer 2005)

Create Function GetNumberOfDaysInTheMonth(@PDate DATETIME) Returns INT

AS

BEGIN 

RETURN CASE WHEN Month(@PDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31

                                WHEN Month(@PDate) IN (4, 6, 9, 11) THEN 30

                                WHEN Month(@PDate)=2 THEN

                    CASE  WHEN (Year(@PDate)%4 = 0)  AND  (Year(@PDate)%100 != 0  OR  Year(@PDate)%400 = 0)                     

                    THEN 29

                    ELSE 28

                    END

 END

END