I have a database consistency issue. (SQL 2K SP2)

[bessebo]

This past week there was a hardware issue that was resolved with our database server. This occurred on Thursday. For the rest of the day Thursday and Friday users transacted as usual with no errors. I perform a weekly database integrity check on all of our production databases on Sunday and also rebuild indexes on those databases. I noticed yesterday that there were errors with the consistency check. They look like the following:

[4] Database 'databasename': Check Data and Index Linkage...

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8909: [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 99, index ID 0, page ID (1:2622568). The PageId in the page header = (2:0).

[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 1434488189, index ID 3: Page (1:2622568) could not be processed. See other errors for details.

[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'ALLOCATION' (object ID 99).

[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'lot_bin_tran' (object ID 1434488189).

[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 2 consistency errors in database 'databasename'.

[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (plt_sensitech ).

I think that I will have to try to repair the database so what I was thinking of doing was restoring the latest backup to our test environment and running the repair in there first. My other question is, is it possible to just live with these issues and not attempt to repair them (as the users are transacting fine thus far)? I also think I have to set the database in single user mode to do this in production so I'll have to do it off-hours. We were thinking of entering a call with Microsoft on this issue. This is the first time in 9 years I have ever had an integrity issue with our production database and I am very concerned.

Regards,
Bessebo

 

[ptheriault]

>>I think that I will have to try to repair the database so what I was thinking of doing was restoring the latest backup to our test environment and running the repair in there first.

If you do that it will correct the problems.

There are two way to fix the problem you have.
1 restore from a backup
2 run checkdb with repair. (Microsoft recommends that you use the restore option as the checkdb with repair could cause data loss.)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bessebo]

Restoring from backup is not an option as there have been two days of transactions that have occurred since I found out that we had a database integrity issue. So I am thinking of running the repair_allow_data_loss option on that database in our test environment to see if that actually works.

I am wondering what the repercussions could be by just not doing anything? We see no errors with the users transacting but, to be honest, I don't like any integrity errors in the database.

Regards,
Bessebo

 

[ptheriault]

You could run a backup now and restore it and the problem would be corrected. Try it on you test server. Take a new backup and restore it on your test server. The restore will correct the problem.

I would not leave it as it is. Just because your application is erroring now doesn't mean it won't.

- Paul
- If at first you don't succeed, find out if the loser gets anything.