none
Could not insert a backup or restore history/detail record in the msdb database. problem with the msdb database

    Question

  • Hi   My backup is failing and having error as follows (verson 2012 RMT 11.0.5058.0)

    10 percent processed.

    20 percent processed.

    30 percent processed.

    40 percent processed.

    50 percent processed.

    60 percent processed.

    70 percent processed.

    81 percent processed.

    91 percent processed.

    Processed 472 pages for database 'master', file 'master' on file 1.

    100 percent processed.

    Processed 2 pages for database 'master', file 'mastlog' on file 1.

    Msg 3009, Level 16, State 1, Line 1

    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.

    BACKUP DATABASE successfully processed 474 pages in 0.182 seconds (20.344 MB/sec).

    Msg 3204, Level 16, State 1, Line 1

    The backup or restore was aborted.

    +++++++++++++++++++++++++++++++++++++++++

    I run following command and came no errors.

    I killed all lock sessions before execute backup.

    DBCC CHECKDB('msdb') WITH ALL_ERRORMSGS
    use msdb
    DBCC CHECKDB

    ++++++++++++++++++++++++++++++++++++++++++++

    still not able to proceed with backup. Greatly appreciate you valuable response.

    thanks

     



    • Edited by ashwan Friday, July 13, 2018 6:04 AM
    Friday, July 13, 2018 6:01 AM

Answers

All replies

  • Ashwan can you show me the backup command you used to run backup. Is this sharepoint database you are backing up, can you show me database name I need to see the length of the database before posting the name here you can change name to any thing but make sure the name length should be same. If the name is too big it might not be able to insert the info about database name into the system tables as it may be exceeding length defined for system tables.

    I would not suggest you to follow what Pinal's article is saying about using trace flag.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Friday, July 13, 2018 7:40 AM
    Moderator
  • Hi Shanky

    This is not share point.

    BACKUP

    DATABASE[master] TO  DISK=N'\\xxxx\master_backup.bak'WITHNOFORMAT,NOINIT,  NAME =N'master-Full Database Backup',SKIP,NOREWIND,NOUNLOAD,  STATS=10


    GO

    declare

    @backupSetId asint

    select

    @backupSetId =position frommsdb..backupset wheredatabase_name=N'master'andbackup_set_id=(selectmax(backup_set_id)frommsdb..backupset wheredatabase_name=N'master')


    if

    @backupSetId isnullbeginraiserror(N'Verify failed. Backup information for database ''master'' not found.',16,1)end

    RESTORE

    VERIFYONLYFROM  DISK=N'\\Backup\master_backup.bak'WITH  FILE=@backupSetId,  NOUNLOAD,  NOREWIND

    GO

    Friday, July 13, 2018 9:54 PM
  • Hi Olaf

    when trace on its works when remove ""RESTORE VERIFYONLYFROM".

    ++++++++++++++++++++++++++++++++++++

    when trace off still issue is occurring for normal backup(no verifyonly) 

    10 percent processed.

    20 percent processed.

    30 percent processed.

    40 percent processed.

    50 percent processed.

    60 percent processed.

    70 percent processed.

    81 percent processed.

    91 percent processed.

    Processed 472 pages for database 'master', file 'master' on file 1.

    100 percent processed.

    Processed 2 pages for database 'master', file 'mastlog' on file 1.

    Msg 3009, Level 16, State 1, Line 1

    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.

    BACKUP DATABASE successfully processed 474 pages in 0.181 seconds (20.456 MB/sec).

    Msg 3204, Level 16, State 1, Line 1

    The backup or restore was aborted.

    Query was cancelled by user.


    Friday, July 13, 2018 10:48 PM
  • 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.

    Do you have a job that purges old backup/restore history from MSDB? If not, then it's time to create one. If you use Ola Hallengren's backup solution, it creates a job that does that.

    1) Back to your issue, can you check if MSDB has grown large? check if there's a large size or a suspicious record in any of the MSDB tables related to backup or restore?

    2) Try running the following to delete backup/restore history older than your desired date and re-run the backup. see if that fixes it.

    --replace date with your desired date. The following deletes history older than June 14 2018
    
    USE msdb
    GO  
    EXEC sp_delete_backuphistory @oldest_date = '06/14/2018'

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Saturday, July 14, 2018 11:42 PM
  • What happens when you simply run

    BACKUP DATABASE[master] TO

    DISK=N'\\xxxx\master_backup.bak'WITHNOFORMAT,NOINIT, SKIP,NOREWIND,NOUNLOAD, STATS=10

    In msdb database how much record is returned when you run select * from msdb.dbo.backupset


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Sunday, July 15, 2018 6:23 AM
    Moderator
  • Check the disk(s) where your msdb files are located (both data and log file). Is there enough free space on the disk?
    Sunday, July 15, 2018 10:25 AM
  • Hi Mohsin

    I feel this your direction is correct in one way. this SQL take  to run long time and no outcome. may be huge amount of records to delete. If do truncate rather delete, then would be work fast. Not sure any option to do so.

    nany thanks

    Monday, July 16, 2018 5:05 AM
  • Its working, but when try to verify backups, then looks suspending the session .
    Monday, July 16, 2018 5:06 AM
  • Hi BalazsThis is I all ready checked. more space on it.
    Monday, July 16, 2018 5:07 AM
  • Its working, but when try to verify backups, then looks suspending the session .
    So just running simple backup command works ? And when you run verify it gives issue ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, July 16, 2018 6:01 AM
    Moderator
  • Hi Mohsin

    Thank you for replay. Well When run this , its hang the process. After that backup also hang. Then need to run DBCC commands to get things normal .Something wrong with msdb Database I think.. Any idea?

    Tuesday, July 24, 2018 12:01 AM
  • Hi Shanky

    To run the backup I need to enable trace traceon(3001,-1). But still verification is an issue. But simple backup works. But I did following and all are hang

    +++++++++++++++++++++++++++++

    USE [master]
    EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'xxxx'
    GO

    ++++++++++++++++

    USE MSDB

    DECLARE @backup_date DATETIME

    BEGIN

    set @backup_date=(select dateadd (dd, -15, getDate()))

    EXEC SP_DELETE_BACKUPHISTORY @backup_date

    END

    +++++++++++++++++++++++++++++++++++

    regards

     


    • Edited by ashwan Tuesday, July 24, 2018 12:05 AM
    Tuesday, July 24, 2018 12:04 AM
  • Ashwan, 

    Take stmt level profiler trace/Ex events with Errors & warnings and point out to the statement which caused the error. 

    Examine the values and INSERT statement to identify the issue.

    Note : Make sure to run the profiler trace in OFF business hours or on a test machine if issue can be reproduced on test machine (by restoring the problematic MSDB  DB)

    Tuesday, July 24, 2018 12:42 AM
  • Hi Shanky

    To run the backup I need to enable trace traceon(3001,-1). But still verification is an issue. But simple backup works. But I did following and all are hang

    +++++++++++++++++++++++++++++

    USE [master]
    EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'xxxx'
    GO

    ++++++++++++++++

    USE MSDB

    DECLARE @backup_date DATETIME

    BEGIN

    set @backup_date=(select dateadd (dd, -15, getDate()))

    EXEC SP_DELETE_BACKUPHISTORY @backup_date

    END

    +++++++++++++++++++++++++++++++++++

    regards

     


    Since simple backup runs successfully I do not see this as issue with msdb database. I also believe when you execute simple backup database command the entry of successful backup is made into msdb database. With query you are trying to remove msdb history and it is hanging, is it getting blocked. 

    PS: Dont leave the thread hanging, please reply quickly so that we can quickly resolve it.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, July 24, 2018 6:48 AM
    Moderator
  • Hi Mohsin

    Thank you for replay. Well When run this , its hang the process. After that backup also hang. Then need to run DBCC commands to get things normal .Something wrong with msdb Database I think.. Any idea?

    Do you see any blocking when it hangs? Hey, by the way, I just re-read your question and you appear to be on SQL 2012 SP2 (Though you said RTM, 5058 is SP2). Why not apply the latest SP4. That way, we rule out a potential root cause. 

    Try applying SP4 and re-try whatever you have been doing and let's see if that fixes it.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, July 24, 2018 11:41 AM
  • Hi Mohsin Need to to verify some application compatibilities first .

    Thursday, July 26, 2018 9:39 PM