February 12, 2010 at 8:27 pm
I am getting the below errors when I run dbcc checkdb on my Database, please let me know how I can resolve these errors. Thanks.
DBCC results for 'Database'.
Msg 8979, Level 16, State 1, Line 1
Table error: Object ID 7, index ID 1, partition ID 458752, alloc unit ID 458752 (type In-row data).
Page (1:102726) is missing references from parent (unknown) and previous (page (1:51089)) nodes.
Possible bad root entry in system catalog.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sysallocunits' (object ID 7).
Msg 7995, Level 16, State 1, Line 1
Database 'Database': consistency errors in system catalogs prevent further DBCC checkdb processing.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'ALLOCATION' (object ID 99).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'Database'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
February 13, 2010 at 6:19 am
Try running dbcc checkdb with REPAIR_REBUILD
http://msdn.microsoft.com/en-us/library/ms176064%28SQL.90%29.aspx
Whatever you do don't run it with REPAIR_ALLOW_DATA_LOSS. You'll hate yourself in the morning.
If the rebuild doesn't fix it you might have to create a new Allocation2 table and select everything from Allocation into it (compare it for accuracy afterward) and then drop allocation and rename allocation2 back to allocation.
This is sucky when FK's are involved but it's better than data loss.
Cheers!!!
Craig Outcalt
February 13, 2010 at 9:17 am
You've got damage to the system tables there. That is not repairable in any way. Get out your latest backup and restore.
If the DB is in full recovery, take a tail log backup first, then restore full backup, latest diff backup if you're using differentials, all log backups ending with the tail log backup that you took. This way you can restore without any data loss.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2010 at 9:25 am
SQLBOT (2/13/2010)
Try running dbcc checkdb with REPAIR_REBUILD
That will fix corruption only when the minimum repair level specified by CheckDB is REPAIR_REBUILD. That will occur when corruption is in nonclustered indexes or the non-leaf levels of the clustered index
Whatever you do don't run it with REPAIR_ALLOW_DATA_LOSS. You'll hate yourself in the morning.
However running CheckDB with repair rebuild is a waste of time when the minimum level to repair is listed as REPAIR_ALLOW_DATA_LOSS or (as in this case) there's no minimum level specified at all.
If the minimum level to repair is listed as REPAIR_ALLOW_DATA_LOSS, running CheckDB with REPAIR_REBUILD will do nothing.
If the rebuild doesn't fix it you might have to create a new Allocation2 table and select everything from Allocation into it (compare it for accuracy afterward) and then drop allocation and rename allocation2 back to allocation.
ALLOCATION is not a table. It refers to the allocation structures of the database, GAM, SGAM, PFS pages and the related system tables. In this case, the system table sysallocunits
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2010 at 6:47 am
Thanks for the reply, it's sql server 2005 version. Actually I have this issue in the actual production environment. So to fix this issue, I took the backup of that production database and restored in Development environment and I want to fix this issue in development environment and then apply the same fix in production enviornment.
How do I find where the problem is? which will help me to fix the errors.
February 14, 2010 at 7:11 am
Mh-397891 (2/14/2010)
How do I find where the problem is? which will help me to fix the errors.
There is damage to the system tables in this database. Specifically sysallocunits. This damage is bad enough that CheckDB cannot even check the rest of the DB for other corruption.
There is no fix for this. There is no repair, there is no manual workaround. Get out your latest backup and restore it. If you've got an unbroken chain of log backups then you can restore full plus those log backups and not lose anything.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2010 at 8:53 am
I ran DBCC CHECKDB (Database,REPAIR_ALLOW_DATA_LOSS) and I got the below error:
DBCC results for 'Database'.
Msg 8979, Level 16, State 1, Line 1
Table error: Object ID 7, index ID 1, partition ID 458752, alloc unit ID 458752 (type In-row data).
Page (1:102726) is missing references from parent (unknown) and previous (page (1:51089)) nodes.
Possible bad root entry in system catalog.
The system cannot self repair this error.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sysallocunits' (object ID 7).
Msg 7995, Level 16, State 1, Line 1
Database 'Database': consistency errors in system catalogs prevent further DBCC checkdb processing.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'ALLOCATION' (object ID 99).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'Database'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Please let me know what I should do.
February 14, 2010 at 9:00 am
Are you completely ignoring everything I'm saying?
This error is not repairable. CheckDB will not fix it. Get out your latest backup and restore the database from backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2010 at 9:24 am
Thanks for your quick responses.
No, I am not ignoring what you are saying, since I am testing in development so I wanted to try the last option of repair_data_loss.
This is what I understood from what you said, please correct me if I am wrong:
1. First I should restore the Latest Full db backup from last night.
2. Then restore differential db backup, then transactional log backups and at the end restore tail log backup(which was taken at the beginning)
Thanks for the help!!
February 14, 2010 at 9:34 am
Mh-397891 (2/14/2010)
Thanks for your quick responses.No, I am not ignoring what you are saying, since I am testing in development so I wanted to try the last option of repair_data_loss.
The error's not repairable in any way. It's system table damage, there are two things (well, two main things) that cause irreparable corruption - system table damage and allocation page damage.
Take a look at this article. http://www.sqlservercentral.com/articles/65804/
This is what I understood from what you said, please correct me if I am wrong:
1. First I should restore the Latest Full db backup from last night.
2. Then restore differential db backup, then transactional log backups and at the end restore tail log backup(which was taken at the beginning)
You need to restore the latest clean backup, the latest one that doen't have corruption. Since you posted here first over a day ago, I'd venture a guess that last night's backup includes the corruption.
Test the friday backup by restoring on your dev system and running CheckDB. If it's clean then you can use that. Also test out the differential backup that you're using as that could potentially include the corruption. Then, once you've identified full (and if applicable diff backups) that are clean, restore them and then restore the entire chain of log backups. If you have an intact log backup chain you can restore this without any data loss
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2010 at 9:48 pm
Just an additional point to Gail's sugggestion is to check with your hardware.check whether you have any hardware issues on your server.If the root cause of your database corruption is due to hardware problems then even if you restore a clean data,the database will again become corrupt :crazy:
February 15, 2010 at 12:05 am
Hi,
Can anyone please let me know what are all the causes for this corruption?
TIA
February 15, 2010 at 12:07 am
Please post new questions in a new thread rather than hijacking an existing, active thread. Thanks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2010 at 2:38 am
Hi,
I really never meant on things like that..Posted only to know the root cause of the issue.
Thanks
April 5, 2010 at 1:55 pm
When we restored the backup's we had some missing data in the database, that might be because of the missing transaction log backups.
So now we are trying to see if there is any way we can fix the checkdb errors which we recieved on the db, I found something online and wondering if this code can fix my errors. Thanks.
use master
GO
sp_configure 'allow updates', 1
RECONFIGURE
GO
ALTER DATABASE Database SET SINGLE_USER WITH NO_WAIT
GO
Use Database
GO
begin tran
delete sys.sysallocunits where ownerid in
(1111
,2222)
delete sys.partitions where partition_id in
(1111
,2222)
delete sys.sysidxstats where id = 11000 and indid in (1,2)
commit tran
GO
drop table [Tbl]
GO
dbcc CHECKALLOC (0, REPAIR_ALLOW_DATA_LOSS)
GO
dbcc CHECKALLOC (0, REPAIR_ALLOW_DATA_LOSS)
GO
use master
GO
ALTER DATABASE Database SET MULTI_USER WITH NO_WAIT
GO
sp_configure 'allow updates', 0
RECONFIGURE
GO
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply