DBCC error in sysindexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

DBCC error in sysindexes

Author

Topic

ttomsen
Starting Member

3 Posts

Posted - 2006-10-10 : 11:03:14

I have some errors in my DB, we do have a backup plan in place, but the person who put it in place is no longer here. It's all automated and running, the problem is , we do A LOT of transactions a day, and the error has been in the db since 9/20(2.5 weeks) when someone shut the power off accidentally. So i'm reluctant to do a backup, mainly since i have NO idea how to do one, we do full and incremental, full on weekend, incremental weeknights.

below is the biggest problem SYSINDEXES :(. a few other tables had problems but DBCC with repair_allow_data_loss has fixed those with 'hardly' any data loss. What can I do here?

THanks in advance

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 2, index ID 0, page ID (1:4917). The PageId in the page header = (1:4925).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2, index ID 255: Page (1:4917) could not be processed. See other errors for details.
DBCC results for 'sysindexes'.
There are 485 rows in 30 pages for object 'sysindexes'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'sysindexes' (object ID 2).

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-10 : 11:32:39

>> So i'm reluctant to do a backup, mainly since i have NO idea how to do one
Learn.
Get someone in for a day who can help you to get out of this problem - that should be your priority.

Looks like you should be considering recreating the database.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ttomsen
Starting Member

3 Posts

Posted - 2006-10-10 : 13:06:43

Thanks for the insight, would people limit their posts to actual solutions, I would appreciate this very much.

I understand the backup/restore process now, it's VERY easy.

I believe the solution i'm going to use is:
1) there is one more corrupt table with inconsistantcies. I'll do the recommended DBCC Checktable('blah', repair_allow...)
2)if sysindexes problem still exists( and I expect it to, as it cannot be repaired) I will recreate database in staging, detach live, switch files for live and staging, then reattach both. Then I will salvage the records I can from previous backup and those records I can salvage out of the production copy.

Kristen
Test

22859 Posts

Posted - 2006-10-10 : 14:05:20

"DBCC with repair_allow_data_loss has fixed those with 'hardly' any data loss"

How do you know what the data was that was lost? IMHO "repair_allow_data_loss" is an absolute last ditch route, and then only when I am able to establish exactly which rows will be lost and sure that I could get them recreated so that the integrity of the database was not breached.

"we do full and incremental, full on weekend, incremental weeknights"

Pity there are no transaction log backups - that might well have enabled you to restore a Full Backup from before the power-outage and all TLog backups since, and quite possibly not lose any data at all

"Thanks for the insight, would people limit their posts to actual solutions, I would appreciate this very much."

I assume the database is offline and any further user access is prohibited, if not you should do that immedaitely and assess what damage is likely to have been caused by continued access

I would recreate the database:

1) Restore an earlier backup (to a DIFFERENT server - lets call it SERVER_B)
2) Script the database
3) Create a new database using the script (on SERVER_B)
3a) This presumes no DDL changes since the last full (clean) backup
4) DTS the data / logins / etc across
5) Backup database on SERVER_B
6) Drop database on original server
6a) If DBCC CHECKDB shows any damage to MASTER / MSDB or MODEL then reinstall instead
7) Restore from backup
8) Sort out any data which could not be rescued in step (4)

You really ought to start this process with a backup taken BEFORE you did the "repair_allow_data_loss" and then if necessary repeat with a current copy of database, and then compare & merge to get the latest data.

Kristen

ttomsen
Starting Member

3 Posts

Posted - 2006-10-10 : 14:29:51

I did row counts on the tables for one, very little loss of rows, second, i can recreate any of the data from audit tables with very little effort.

Thanks for your suggestion on how to do the restore, I will be using something very simular.

Tyler

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources