November 27, 2012 at 12:23 pm
Hello,
Whenever I run my full backup maintainance plan job the backups get completed successfully but the job fails with error
A time-out occurred while waiting for buffer latch -- type 4, bp 00000000A9FABF40, page 1:2438, stat 0xc00009, database id: 4, allocation unit Id: 72057594045267968, task 0x0000000000AE9288 : 0, waittime 300, flags 0x200000001a, owning task 0x0000000000AE9288. Not continuing to wait..
I am able to do a database restore without any issue. per the error above I wanted to know if there are any issues with the database id 4 which is MSDB by running dbcc checkdb('MSDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; and I came up with the following errors.
Msg 8925, Level 16, State 1, Line 1
Table error: Cross object linkage: Page (1:2037), slot 2, in object ID 46623209, index ID 2, partition ID 72057594043367424, alloc unit ID 72057594044809216 (type In-row data), refers to page (1:2323), slot 0, in object ID 1606296782, index ID 2, partition ID 72057594057719808, alloc unit ID 72057594064076800 (type In-row data).
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 46623209, index ID 2, partition ID 72057594043367424, alloc unit ID 72057594044809216 (type In-row data). Page (1:2039) is missing a reference from previous page (1:2323). Possible chain linkage problem.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 46623209, index ID 2, partition ID 72057594043367424, alloc unit ID 72057594044809216 (type In-row data). Page (1:2249) is missing a reference from previous page (1:2324). Possible chain linkage problem.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2323) allocated to object ID 46623209, index ID 2, partition ID 72057594043367424, alloc unit ID 72057594044809216 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8910, Level 16, State 1, Line 1
Page (1:2323) in database ID 4 is allocated to both object ID 46623209, index ID 2, partition ID 72057594043367424, alloc unit ID 72057594044809216 (type In-row data), and object ID 1606296782, index ID 2, partition ID 72057594057719808, alloc unit ID 72057594064076800 (type In-row data).
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2324) allocated to object ID 46623209, index ID 2, partition ID 72057594043367424, alloc unit ID 72057594044809216 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8910, Level 16, State 1, Line 1
Page (1:2324) in database ID 4 is allocated to both object ID 46623209, index ID 2, partition ID 72057594043367424, alloc unit ID 72057594044809216 (type In-row data), and object ID 149575571, index ID 1, partition ID 72057594039828480, alloc unit ID 72057594040877056 (type Row-overflow data).
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 46623209, index ID 2, partition ID 72057594043367424, alloc unit ID 72057594044809216 (type In-row data). Page (1:2324) was not seen in the scan although its parent (1:2037) and previous (1:2039) refer to it. Check any previous errors.
CHECKDB found 2 allocation errors and 6 consistency errors in table 'backupmediaset' (object ID 46623209).
Msg 8910, Level 16, State 1, Line 1
Page (1:2327) in database ID 4 is allocated to both object ID 110623437, index ID 2, partition ID 72057594043498496, alloc unit ID 72057594044940288 (type In-row data), and object ID 206623779, index ID 2, partition ID 72057594043629568, alloc unit ID 72057594045071360 (type In-row data).
Msg 8937, Level 16, State 1, Line 1
Table error: Object ID 110623437, index ID 2, partition ID 72057594043498496, alloc unit ID 72057594044940288 (type In-row data). B-tree page (1:2327) has two parent nodes (1:2030), slot 2 and (1:2186), slot 1.
CHECKDB found 1 allocation errors and 1 consistency errors in table 'backupmediafamily' (object ID 110623437).
Msg 8910, Level 16, State 2, Line 1
Page (1:2426) in database ID 4 is allocated to both object ID 149575571, index ID 1, partition ID 72057594039828480, alloc unit ID 72057594040877056 (type Row-overflow data), and object ID 149575571, index ID 1, partition ID 72057594039828480, alloc unit ID 72057594040877056 (type Row-overflow data).
CHECKDB found 1 allocation errors and 0 consistency errors in table 'sysjobhistory' (object ID 149575571).
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 206623779, index ID 1, partition ID 72057594043564032, alloc unit ID 72057594045005824 (type In-row data). Page (1:2441) was not seen in the scan although its parent (1:1984) and previous (1:2440) refer to it. Check any previous errors.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 206623779, index ID 1, partition ID 72057594043564032, alloc unit ID 72057594045005824 (type In-row data). Index node page (1:1984), slot 65 refers to child page (1:2442) and previous child (1:2441), but they were not encountered.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 206623779, index ID 2, partition ID 72057594043629568, alloc unit ID 72057594045071360 (type In-row data). Page (1:2254) is missing a reference from previous page (1:2327). Possible chain linkage problem.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2327) allocated to object ID 206623779, index ID 2, partition ID 72057594043629568, alloc unit ID 72057594045071360 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'backupset' (object ID 206623779).
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 366624349, index ID 1, partition ID 72057594043826176, alloc unit ID 72057594045267968 (type In-row data). Page (1:2438) was not seen in the scan although its parent (1:1885) and previous (1:2437) refer to it. Check any previous errors.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 366624349, index ID 1, partition ID 72057594043826176, alloc unit ID 72057594045267968 (type In-row data). Index node page (1:1885), slot 78 refers to child page (1:2439) and previous child (1:2438), but they were not encountered.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'backupfile' (object ID 366624349).
Msg 8926, Level 16, State 3, Line 1
Table error: Cross object linkage: Parent page (0:1), slot 0 in object 1606296782, index 2, partition 72057594057719808, AU 72057594064076800 (In-row data), and page (1:2253)->next in object 46623209, index 2, partition 72057594043367424, AU 72057594044809216 (In-row data), refer to page (1:2323) but are not in the same object.
Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1606296782, index ID 2, partition ID 72057594057719808, alloc unit ID 72057594064076800 (type In-row data). B-tree chain linkage mismatch. (1:2253)->next = (1:2323), but (1:2323)->Prev = (1:1811).
Msg 8937, Level 16, State 1, Line 1
Table error: Object ID 1606296782, index ID 2, partition ID 72057594057719808, alloc unit ID 72057594064076800 (type In-row data). B-tree page (1:2323) has two parent nodes (0:1), slot 0 and (1:1636), slot 4.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'DTA_reports_column' (object ID 1606296782).
CHECKDB found 4 allocation errors and 16 consistency errors in database 'msdb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (msdb).
This is not our production environment but staging. Hence I am not backing up my system databases for this server. So I dont have an older backup for MSDB. What could be the best way to fix this? ANy inputs are highly appreciated.
Thanks a bunch
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
November 27, 2012 at 2:25 pm
You definitely have some issues with the data in your MSDB table.
I'm pretty certain the reason your backup completes successfully but your maintenance plan job fails is because you have the "Verify backup Integrity" option selected.
If you have no prior backup then you're out of luck with restoring the "bad" data. In cases like this the only option you have is to repair the database with the REPAIR_ALLOW_DATA_LOSS option turned on, this will most likely remove the bad records (most always), but will also fix your consistency and allocation errors.
From what I can see in your error except, worst case scenario would be you would lose some SQL Agent Job history, and some database back up job info...but I'm no expert in this area
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 27, 2012 at 4:03 pm
CheckDB with repair allow data loss option fixed the errors. My job was able to finish fine now.
USE [master]
GO
ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [msdb] SET SINGLE_USER
GO
DBCC CHECKDB (msdb, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE [msdb] SET MULTI_USER
Thanks for your inputs
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply