October 10, 2008 at 5:01 am
hi,
one of our databases log was corrupted and we tried to rebuild database log but got this allocation error. what's the best way to fix this?
DBCC results for 'AR'.
***************************************************************
Table sysobjects Object ID 1.
Index ID 1. FirstIAM (1:99). Root (1:186). Dpages 6.
Index ID 1. 7 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:15). Root (1:14). Dpages 3.
Index ID 2. 5 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:34). Root (1:33). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysindexes Object ID 2.
Index ID 1. FirstIAM (1:12). Root (1:13). Dpages 18.
Index ID 1. 18 pages used in 3 dedicated extents.
Index ID 255. FirstIAM (1:28). Root (1:16). Dpages 0.
Index ID 255. 81 pages used in 13 dedicated extents.
Total number of extents is 16.
***************************************************************
Table syscolumns Object ID 3.
Index ID 1. FirstIAM (1:26). Root (1:27). Dpages 134.
Index ID 1. 118 pages used in 18 dedicated extents.
Index ID 2. FirstIAM (1:36). Root (1:35). Dpages 54.
Index ID 2. 56 pages used in 10 dedicated extents.
Total number of extents is 28.
***************************************************************
Table systypes Object ID 4.
Index ID 1. FirstIAM (1:30). Root (1:31). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:38). Root (1:37). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table syscomments Object ID 6.
Index ID 1. FirstIAM (1:47). Root (1:48). Dpages 113.
Index ID 1. 103 pages used in 16 dedicated extents.
Total number of extents is 16.
***************************************************************
Table sysfiles1 Object ID 8.
Index ID 0. FirstIAM (1:187). Root (1:32). Dpages 2.
Index ID 0. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table syspermissions Object ID 9.
Index ID 1. FirstIAM (1:50). Root (1:51). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysusers Object ID 10.
Index ID 1. FirstIAM (1:53). Root (1:54). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:45). Root (1:44). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:56). Root (1:55). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysproperties Object ID 11.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysdepends Object ID 12.
Index ID 1. FirstIAM (1:11). Root (1:73). Dpages 9.
Index ID 1. 11 pages used in 1 dedicated extents.
Index ID 2. FirstIAM (1:80). Root (1:75). Dpages 6.
Index ID 2. 8 pages used in 0 dedicated extents.
Total number of extents is 1.
Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:33303) object ID 112016176 index ID 0 but was not detected in the scan.
***************************************************************
Table sysreferences Object ID 14.
Index ID 1. FirstIAM (1:63). Root (1:64). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:58). Root (1:57). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Index ID 3. FirstIAM (1:66). Root (1:65). Dpages 1.
Index ID 3. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfulltextcatalogs Object ID 19.
Index ID 1. FirstIAM (1:70). Root (1:71). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:68). Root (1:67). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfulltextnotify Object ID 24.
Index ID 1. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 1. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table sysfilegroups Object ID 96.
Index ID 1. FirstIAM (1:42). Root (1:43). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 2. FirstIAM (1:40). Root (1:39). Dpages 1.
Index ID 2. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
CHECKALLOC found 1 allocation errors and 0 consistency errors in table '(Object ID 112016176)' (object ID 112016176).
***************************************************************
Table T_AR_Discounts Object ID 359672329.
Index ID 0. FirstIAM (0:0). Root (0:0). Dpages 0.
Index ID 0. 0 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table T_AR_Hdr Object ID 429244584.
Index ID 0. FirstIAM (1:121). Root (1:10110). Dpages 5603.
Index ID 0. 5606 pages used in 806 dedicated extents.
Index ID 2. FirstIAM (1:155). Root (1:153). Dpages 1255.
Index ID 2. 1274 pages used in 467 dedicated extents.
Index ID 40. FirstIAM (1:127). Root (1:73405). Dpages 934.
Index ID 40. 946 pages used in 162 dedicated extents.
Total number of extents is 1435.
***************************************************************
Table T_AR_PDC Object ID 877246180.
Index ID 1. FirstIAM (1:297). Root (1:296). Dpages 66.
Index ID 1. 68 pages used in 13 dedicated extents.
Total number of extents is 13.
***************************************************************
Table TbHCreditStatus Object ID 907150277.
Index ID 0. FirstIAM (1:96). Root (1:87). Dpages 145.
Index ID 0. 146 pages used in 69 dedicated extents.
Total number of extents is 69.
***************************************************************
Table T_AR_Dtl Object ID 912722304.
Index ID 0. FirstIAM (1:182). Root (1:181). Dpages 3036.
Index ID 0. 3039 pages used in 583 dedicated extents.
Index ID 2. FirstIAM (1:185). Root (1:184). Dpages 1248.
Index ID 2. 1268 pages used in 461 dedicated extents.
Index ID 3. FirstIAM (1:225). Root (1:190). Dpages 1214.
Index ID 3. 1236 pages used in 457 dedicated extents.
Index ID 21. FirstIAM (1:134). Root (1:31634). Dpages 485.
Index ID 21. 489 pages used in 162 dedicated extents.
Index ID 22. FirstIAM (1:5194). Root (1:32140). Dpages 716.
Index ID 22. 723 pages used in 223 dedicated extents.
Total number of extents is 1886.
***************************************************************
Table dtproperties Object ID 1205579333.
Index ID 1. FirstIAM (1:129). Root (1:130). Dpages 1.
Index ID 1. 3 pages used in 0 dedicated extents.
Index ID 255. FirstIAM (1:125). Root (1:124). Dpages 0.
Index ID 255. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Table T_AR_Open Object ID 1561772621.
Index ID 1. FirstIAM (1:113). Root (1:112). Dpages 28110.
Index ID 1. 28574 pages used in 3574 dedicated extents.
Index ID 55. FirstIAM (1:33304). Root (1:32745). Dpages 5504.
Index ID 55. 5578 pages used in 698 dedicated extents.
Index ID 56. FirstIAM (1:33305). Root (1:38193). Dpages 3104.
Index ID 56. 3143 pages used in 398 dedicated extents.
Index ID 57. FirstIAM (1:33306). Root (1:49843). Dpages 4096.
Index ID 57. 4159 pages used in 527 dedicated extents.
Index ID 58. FirstIAM (1:33307). Root (1:60716). Dpages 4018.
Index ID 58. 4082 pages used in 515 dedicated extents.
Index ID 59. FirstIAM (1:33308). Root (1:66749). Dpages 4053.
Index ID 59. 4124 pages used in 521 dedicated extents.
Index ID 60. FirstIAM (1:33309). Root (1:77650). Dpages 6848.
Index ID 60. 6973 pages used in 873 dedicated extents.
Index ID 61. FirstIAM (1:33311). Root (1:82513). Dpages 5882.
Index ID 61. 5972 pages used in 748 dedicated extents.
Total number of extents is 7854.
***************************************************************
Table TbSARSetup Object ID 1785773419.
Index ID 0. FirstIAM (1:154). Root (1:114). Dpages 1.
Index ID 0. 2 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
Processed 48 entries in sysindexes for database ID 8.
File 1. Number of extents = 11673, used pages = 80403, reserved pages = 93377.
File 1 (number of mixed extents = 36, mixed pages = 281).
Object ID 1, Index ID 0, data extents 0, pages 7, mixed extent pages 7.
Object ID 1, Index ID 2, index extents 0, pages 5, mixed extent pages 5.
Object ID 1, Index ID 3, index extents 0, pages 2, mixed extent pages 2.
Object ID 2, Index ID 0, data extents 3, pages 18, mixed extent pages 8.
Object ID 2, Index ID 255, index extents 13, pages 81, mixed extent pages 9.
Object ID 3, Index ID 0, data extents 18, pages 118, mixed extent pages 9.
Object ID 3, Index ID 2, index extents 10, pages 56, mixed extent pages 9.
Object ID 4, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 4, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 6, Index ID 0, data extents 16, pages 103, mixed extent pages 9.
Object ID 8, Index ID 0, data extents 0, pages 2, mixed extent pages 2.
Object ID 9, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 10, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 10, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 10, Index ID 3, index extents 0, pages 2, mixed extent pages 2.
Object ID 12, Index ID 0, data extents 1, pages 11, mixed extent pages 9.
Object ID 12, Index ID 2, index extents 0, pages 8, mixed extent pages 8.
Object ID 14, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 14, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 14, Index ID 3, index extents 0, pages 2, mixed extent pages 2.
Object ID 19, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 19, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 96, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 96, Index ID 2, index extents 0, pages 2, mixed extent pages 2.
Object ID 99, Index ID 0, data extents 1, pages 7, mixed extent pages 1.
Object ID 112016176, Index ID 0, data extents 318, pages 2546, mixed extent pages 2.
Object ID 429244584, Index ID 0, data extents 806, pages 5606, mixed extent pages 9.
Object ID 429244584, Index ID 2, index extents 467, pages 1274, mixed extent pages 9.
Object ID 429244584, Index ID 40, index extents 162, pages 946, mixed extent pages 9.
Object ID 877246180, Index ID 0, data extents 13, pages 68, mixed extent pages 9.
Object ID 907150277, Index ID 0, data extents 69, pages 146, mixed extent pages 9.
Object ID 912722304, Index ID 0, data extents 583, pages 3039, mixed extent pages 9.
Object ID 912722304, Index ID 2, index extents 461, pages 1268, mixed extent pages 9.
Object ID 912722304, Index ID 3, index extents 457, pages 1236, mixed extent pages 9.
Object ID 912722304, Index ID 21, index extents 162, pages 489, mixed extent pages 9.
Object ID 912722304, Index ID 22, index extents 223, pages 723, mixed extent pages 9.
Object ID 1205579333, Index ID 0, data extents 0, pages 3, mixed extent pages 3.
Object ID 1205579333, Index ID 255, index extents 0, pages 2, mixed extent pages 2.
Object ID 1561772621, Index ID 0, data extents 3574, pages 28574, mixed extent pages 9.
Object ID 1561772621, Index ID 55, index extents 698, pages 5578, mixed extent pages 9.
Object ID 1561772621, Index ID 56, index extents 398, pages 3143, mixed extent pages 9.
Object ID 1561772621, Index ID 57, index extents 527, pages 4159, mixed extent pages 9.
Object ID 1561772621, Index ID 58, index extents 515, pages 4082, mixed extent pages 9.
Object ID 1561772621, Index ID 59, index extents 521, pages 4124, mixed extent pages 9.
Object ID 1561772621, Index ID 60, index extents 873, pages 6973, mixed extent pages 9.
Object ID 1561772621, Index ID 61, index extents 748, pages 5972, mixed extent pages 9.
Object ID 1785773419, Index ID 0, data extents 0, pages 2, mixed extent pages 2.
Total number of extents = 11673, used pages = 80403, reserved pages = 93377 in this database.
(number of mixed extents = 36, mixed pages = 281) in this database.
CHECKALLOC found 1 allocation errors and 0 consistency errors in database 'AR'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKALLOC (AR ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
October 10, 2008 at 10:51 am
Well, the best course of action is to restore from your backups. I'm guessing you don't have any because you took the destructive step of rebuilding your transaction log. If you have no backups, the only way to recover from this error is to run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option - really a must-do after rebuilding a transaction log.
See http://www.sqlskills.com/blogs/paul/2007/09/15/CorruptionLastResortsThatPeopleTryFirst.aspx
You also need to figure out why the log became corrupt in the first place and take corrective action to ensure it doesn't happen again - most likely I/O subsystem problems.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 13, 2008 at 3:21 am
Hi Paul,
Thank you so much for responding. But the cause of error was due to the insuffie\cient disk space that log has grown to a tremendous 25gb size. So I deleted the log and tried to rebuild the database and log and just rename the database. I cannot try the repair yet due to heavy transactions that client has. And although this allocation error is existing, we are not encountering problems on our system. Is this an alarming error?
October 13, 2008 at 3:40 am
It's a serious error. You may not be encountering errors now, but of someone tries to use the corrupt table, they will get errors. Since it's sysdepends, that may happen when creating tables, views or other objects
The corruption looks like it's in the system tables (sysdepends). I don't think that checkDB is allowed to repair that table (Paul will be able to say for sure)
When you have some downtime, can you run a full checkDB to see if there are other errors?
DBCC CHECKDB('AR') WITH NO_INFOMSGS
Do you have a clean 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
October 13, 2008 at 7:31 am
Why did you do that? And how did you delete the log? Using BACKUP LOG... WITH NOLOG or actually rebuilding it with DBCC?
Your log is probably growing because you're not taking log backups. See http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-23-My-transaction-log-is-full-now-what.aspx
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 14, 2008 at 2:53 am
Hi Paul,
We do full backups(.bak). I did tried restoring the back and still use the current .df and use the old .ldf, error says files are not associated. That's why I prompted to create a new .ldf out of the .mdf.
While detaching the database, we encountered an error "Database failed to dettach" but the database was still dettached and i couldn't attach it again. So i deleted the LOG .ldf(which I usually do) and attached the .mdf in Eterprise Manager which will automatically create a new log (supposedly) but didn't due the error encountered earlier during dettach.
As of the moment, users have no complains on system. But I still wanna fix the allocation issue.
October 14, 2008 at 3:05 am
We do full backups(.bak). I did tried restoring the back and still use the current .df and use the old .ldf, error says files are not associated.
What were you trying to achieve by doing that?
Can you please post the full output of CheckDB?
DBCC CHECKDB('AR') WITH NO_INFOMSGS
The best way to fix this would be to restore the last good full backup and then apply your transaction log backups. You won't be able to roll forward after the point where you deleted the log, as that broke the log chain. That's assuming you have log backups.
Is that an option?
Paul: Can CheckDB repair sysdepends?
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
October 14, 2008 at 10:12 am
Sysdepends - don't remember in 2000 - and I don't have a 2000 server handy to try it on.
May Anne - if your database is in the FULL recovery model and you're only doing full backups, you run the risk of your log running out of space - as you experienced. You must take log backups also when in the FULL recovery model - of just switch back to SIMPLE if you're not interested in the capabilities they give you.
Detach and delete the log is about the worst thing you can do to a database that has an out-of-space log. It will not re-attach because it wasn't cleanly shutdown. See http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 15, 2008 at 1:21 am
Hi Gil,
Here it is:
Server: Msg 2576, Level 16, State 1, Line 1
IAM page (0:0) is pointed to by the previous pointer of IAM page (1:33303) object ID 112016176 index ID 0 but was not detected in the scan.
CHECKDB found 1 allocation errors and 0 consistency errors in table '(Object ID 112016176)' (object ID 112016176).
CHECKDB found 1 allocation errors and 0 consistency errors in database 'AR'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AR ).
Hi Paul,
I've been deleting .ldf files whenever it's size is way too big and eterprises manager simply creates a new one. and the issue is not the log space that run out but the disk space, which didn't allow .ldf file to grow and write.
Please see attached how I did it on an AR backup that's not corrupted.
October 15, 2008 at 1:35 am
May Anne Duran (10/15/2008)
I've been deleting .ldf files whenever it's size is way too big and eterprises manager simply creates a new one. and the issue is not the log space that run out but the disk space, which didn't allow .ldf file to grow and write.
Ok, that is not how you manage logs in SQL Server. It's asking for trouble. If the DB is in full recovery, you should have log backups running. That will keep the log size manageable. Add to that the log should be on a drive that's big enough that the log won't run out of space during normal activity.
See the link Paul gave above and also see this, should give you a good understanding of managing transaction logs
http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/
Now, as for the corruption:
If you run the following in the AR database, what do you get?
SELECT object_name(112016176)
The screens you showed were from attaching the DB. I'm asking about backups. Do you have a database backup from before the corruption that you can restore?
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
October 15, 2008 at 2:02 am
Hi Gil,
OK I get your point.
Yes, I have a backup that i can rstore from. But a half day's worth of data would be lost because backup is from midday and corruption occurred in the evening.
October 15, 2008 at 2:42 am
I would suggest that you rename the corrupt DB, then restore the clean backup along side it. Run CheckDB to make sure there's no corruption. If it's clean, then see if you can copy over the half-day's data.
Running repair (your only other option) will discard data. Hard to tell how much. I'm not sure it will repair successfully, because of the damage to the system tables.
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
October 15, 2008 at 7:03 am
May Anne Duran (10/15/2008)
Hi Gil,OK I get your point.
Yes, I have a backup that i can rstore from. But a half day's worth of data would be lost because backup is from midday and corruption occurred in the evening.
This is why you take log backups - so you can restore up to the point in time of the corruption and not lose 1/2 a day's work.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply