locked
troubleshoot one transacion log job failure--need help RRS feed

  • Question

  • Setup transaction log backup of all databases on sql server every hour.

    3:00 am transation log backup job fails. Then Success at 4:00 am,5:00 am ,6:00 am, 7:00 am...

    I check job history of 3:00 am failure and find following message:

    [SQLSTATE 01000] (Message 4035)  Transaction (Process ID 361) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205)  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. [SQLSTATE 42000] (Error 3009)  BACKUP LOG successfully processed 1 pages in 0.003 seconds (1.790 MB/sec). [SQLSTATE 01000] (Error 3014).  The step failed.

    What's happened? How to troubeshoot/fix this issue? I have no idea here.

    Sunday, January 19, 2014 1:36 PM

Answers

  • As the error message says, the backup of the log did not fail, only the recording of the fact that it happened did. That is, you will still be able to restore the database to a point-in-time if needed. However, it will take some more work, because the UI will not include the log that was not recorded, but you would have to add that manually. (Which can be quite of a pain in the stressful situation after a disaster.)

    The msdb database is not exactly known for being performance-optimized, but as the backup history grows, performance degrades, and the windows for deadlock increases.

    Since I don't work as a DBA myself, it is not a painpoint that I have personal experience of, nor do I have any tricks up my own sleeve. But I went to Google and searched on "msdb indexes" and the top post includes a script from my MVP colleague Geoff Hiten, so it can't be bad.
    http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Tuesday, January 21, 2014 9:48 AM
    • Marked as answer by Fanny Liu Tuesday, February 4, 2014 2:57 AM
    Sunday, January 19, 2014 5:37 PM

All replies

  • I have seen the message , but did nothing except DBCC CHECKDB on msdb, just make sure you can recovery the database till 3am (stop at clause)

    What account that SQL Agent runs under? Memeber of sysadmin server role?

    Looks like it failed to insert the backup history, run DBCC CHECKDB on msdb


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Sunday, January 19, 2014 1:45 PM
    Answerer
  • It is production box. If I run DBCC CheckDB on MSDB, will any problem be caused potientially?

    And this job runs under "sa".

    Sunday, January 19, 2014 2:41 PM
  • It is production box. If I run DBCC CheckDB on MSDB, will any problem be caused potientially?

    And this job runs under "sa".


    No it will not cause any problem as such as a fact you should run checkdb for system databases.But behavior of checkdb when run on tempdb is different .It takes lock instead of creating snapshot.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, January 19, 2014 4:12 PM
  • As the error message says, the backup of the log did not fail, only the recording of the fact that it happened did. That is, you will still be able to restore the database to a point-in-time if needed. However, it will take some more work, because the UI will not include the log that was not recorded, but you would have to add that manually. (Which can be quite of a pain in the stressful situation after a disaster.)

    The msdb database is not exactly known for being performance-optimized, but as the backup history grows, performance degrades, and the windows for deadlock increases.

    Since I don't work as a DBA myself, it is not a painpoint that I have personal experience of, nor do I have any tricks up my own sleeve. But I went to Google and searched on "msdb indexes" and the top post includes a script from my MVP colleague Geoff Hiten, so it can't be bad.
    http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Tuesday, January 21, 2014 9:48 AM
    • Marked as answer by Fanny Liu Tuesday, February 4, 2014 2:57 AM
    Sunday, January 19, 2014 5:37 PM
  • that's correct,first you need to run checkdb to see is there any issues, since it is occurring during at here -

    Could not insert a backup or restore history/detail record in the msdb database.>>check your msdb file settings, also

    is the history cleanup exists(like to cleanup older than X days) to remove older than some days, if not then set it.

    Looks like during the same time the other transaction is holding the lock on it, so if it's dbcc checkdb did gives any error then apply the trace flags 1204,1222

    http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

    hope fully your sql server is >2000


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Monday, January 20, 2014 6:19 PM