3

Today I found msdb database was in suspected mode, so I restored MSDB from my latest backup(As I have daily backup for this database). When I restore the MSDB database using Tsql i got below errors, but database restore was successful.

Processed 17056 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 5 pages for database 'msdb', file 'MSDBLog' on file 1.
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__backupse__21F79AAB0E391C95'. Cannot insert duplicate key in object 'dbo.backupset'. The duplicate key value is (60979).
Msg 3009, Level 16, State 1, Line 2
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
The statement has been terminated.
RESTORE DATABASE successfully processed 17061 pages in 6.953 seconds (19.169 MB/sec).

But after restore I found that SQL server instance is running proper and all database are accessible. But to ensure about the error I got during restore I used CHECKDB command for the MSDB database.It shows below errors.

DBCC results for 'msdb'.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 2515, Level 16, State 1, Line 1
The page (1:1044), object ID 60, index ID 1, partition ID 281474980642816, allocation unit ID 281474980642816 (type In-row data) has been modified, but is not marked as modified in the differential backup bitmap.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'msdb'.

So what are these errors? Also is it due to the error I got during restore? Is there anything I need to do as I got error while restore?

  • My initial thought is that this backup may also be corrupt. Do you have a backup made before the last known good DBCC CHECKDB you've made for msdb on this instance? – MattyZDBA Oct 13 '14 at 13:44
  • @MattyZDBA Yes. I used to backup msdb daily once. But can I ignore this error? Is that serious error? – IT researcher Oct 13 '14 at 13:54
2

Violation of PRIMARY KEY constraint 'PK__backupse__21F79AAB0E391C95'. Cannot insert duplicate key in object 'dbo.backupset'. The duplicate key value is (60979).

This might happen if someone have done a reseed on the dbo.backupset.

From table definition :

PK__backupse* --> clustered, unique, primary key located on PRIMARY --> backup_set_id

To fix that error :

--- get the max backup set id from the backupset table 

    SELECT MAX(backup_set_id) + 1 FROM msdb.dbo.backupset

 --- then reseed it

   dbcc checkident('dbo.backupset', RESEED, VALUE_FROM_ABOVE)

Now run DBCC CHECKDB('msdb') WITH NO_INFOMSGS, ALL_ERRORMSGS to see if everything returns fine.

Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Try running DBCC CHECKDB with TABLOCK hint + make sure that the drive tempdb resides has enough space and tempdb is not having restrictive growth (autogrowth OFF).

Lastly,

Msg 2515, Level 16, State 1, Line 1 The page (1:1044), object ID 60, index ID 1, partition ID 281474980642816, allocation unit ID 281474980642816 (type In-row data) has been modified, but is not marked as modified in the differential backup bitmap. CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).

If you run DBCC CHECKDB('msdb') WITH TABLOCK, NO_INFOMSGS, ALL_ERRORMSGSand still it gives error, then its most likely a problem with your IO subsystem. Try to check windows event logs.

Have Page checksums enabled. Also, take a full backup of msdb (which will reset differential bitmap) and then run DBCC CHECKDB('msdb') WITH TABLOCK, NO_INFOMSGS, ALL_ERRORMSGS

In an unlikely event, you should try going backward and find a good backup to restore.

  • Hey.. Now for some of the databases when I do log backup I get error like "BACKUP detected corruption in the database log. Check the errorlog for more information." But full backup works properly. Is this error is due to the problems present in msdb database? or it may be different error? – IT researcher Oct 15 '14 at 12:03
  • Different errors. Can you try restoring on a different server and take full and log backups? Something is not right - io subsystem may be ? – Kin Shah Oct 15 '14 at 12:24
  • I will try and get back to you – IT researcher Oct 15 '14 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.