Dbcc Checktable - Error

[anam]

Greetings,

A user was getting 605 fatal warning message trying to access a table. After I ran DBCC checktable ('table_name'), I got the following error message:

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 302055424, index ID 451, page ID (1:93420). The PageId in the page header = (17741:1095655263).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1106102981, index ID 0: Page (1:93420) could not be processed. See other errors for details.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table '(Object ID 302055424)' (object ID 302055424).
DBCC results for 'tbl_Events'.
There are 720533 rows in 11439 pages for object 'tbl_Events'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'tbl_Events' (object ID 1106102981).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (DB_INFO.dbo.tbl_Events ).

Could you please inform me of the steps which I need to take to fix the problem.

Thanks.

LS

[skhanal]

If you can save the table data into a new table

select * into newtable
from tbl_Events

drop tbl_Events and recreate it. Then copy data from newtable.

If it does not work, try restoring the database in a test environment and copy the table data to the production. But make sure that it does not cause consistency problem with other tables in the database.

Your last resort it to run

DBCC CHECK TABLE WITH REPAIR_ALLOW_DATA_LOSS

option. Make sure you issule begin tran before running it so that you can rollback if you have to.

[MAK]

I will do this first.

Backup the database.
DBCC CHECKTABLE REPAIR_FAST

If it is not fixed. then I will do this.
DBCC CHECKTABLE REPAIR_REBUILD

If it is not fixed. then I will do this.
restore the database in a staging box and do the following.

DBCC CHECKTABLE REPAIR_ALLOW_DATA_LOSS

Compare the data between production and staging.

If it is not fixed. buy Log Explorer and get all the data in the form of SQL Statements.

If you are ok with the data loss, then no need to buy the software.

[anam]

I got OK from user to do my work,

I did type :
DBCC checktable ('tbl_events', repair_fast)

and got an error stating that I need to be in a single mode, I put the database in single mode but still gives me the same error

Thanks.

[skhanal]

run sp_who to see who else is connected.

[anam]

I have but still gets me the message that the database needs to be placed in single mode!

Lava

[skhanal]

In Query Analyzer run this to verify the database is in single user mode

USE DBName
SELECT DATABASEPROPERTY('dbname', 'IsSingleUser')

If it returns 0,

run

alter database DBNAME set single_user with rollback immediate

and check databaseproperty again.

[anam]

Thanks skhanal. I had to close all my sessions and reopen them to by pass the message, for some reason I was getting the error even though the DB was in single mode.

Thanks for your time. I did repair_fast and that solved the problem.

Take care.
LS