Sql-server – “Could not insert a backup or restore history/detail record in the msdb database” While restoring msdb

dbcc-checkdbmsdbrestoresql serversql-server-2008-r2

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?

Best Answer

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.

Related Question