locked
Issue with SQL backup job RRS feed

  • Question

  • I am experiencing a weird issue with BizTalk 2010 and SQL Server 2008.
    The SQL job named "Backup BizTalk Server (BizTalkMgmtDb)" throws the following error on step 3 (MarkAndBackupLog):

    "Processed 17 pages for database 'BizTalkMgmtDb', file 'BizTalkMgmtDb_log' on file 1. [SQLSTATE 01000] (Message 4035) The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. [SQLSTATE 42000] (Error 4208) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) BACKUP LOG successfully processed 17 pages in 0.022 seconds (6.014 MB/sec). [SQLSTATE 01000] (Error 3014). The step failed."

    I have checked the recovery model for the BizTalkMgmtDb and it is set to full. I have also compared settings to another SQL server in a different environment in which the job is working fine, and everything looks the same.

    Any clue of what is happening ? Any help/advice/insight would be really appreciated.

    Thanks !

    Wednesday, December 12, 2012 2:31 PM

Answers

  • Hi,

    Changing the recovery model from full to simple and back again will require a full backup to be done before any log backups will complete.  You can do this by setting the ForceFull column in the management database's adm_ForceFullBackup table to True or 1 and then executing the BizTalk backup SQL Agent job afterwards.  From then on the BizTalk backup job should complete as normal.

    Wednesday, December 12, 2012 6:47 PM

All replies

  • Few things you should check 

    1. Size of your log file for (Messagebox and DTADB or BAMPrimaryImport) , If it is very large then you should consider to truncate it.
    2. Free space of the drive where you are keeping your backup files, May be there is no more free space, you should consider deleting some old backup files to make some space.
    3. You should monitor status  of the  Backup Jobs regularly, to avoid situation like this in future.
    4. Run BizTalk MessageBox Viewer Tool to check the health of your BizTalk setup and resolve any issues reported. 

    There is a TN wiki BizTalk Databases: Survival Guide, It has some useful info which you may need. Have a look

    I hope this helps you. Let is know if you get more details. 


    HTH,
    Naushad Alam

    When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer
    alamnaushad.wordpress.com


    Wednesday, December 12, 2012 3:44 PM
    Moderator
  • Thank you for your answer Naushad !

    I have checked the disk space and there are several Gb free on each of the servers drives. I am working on a test environment, so the log files size are not exceeding 52 Mb.

    One thing I didn't mention is that the recovery model of BizTalkMgmtDb has recently been changed from Full to Simple, then back to Full. Could that affect the backup job ?

    Thanks !

    Wednesday, December 12, 2012 5:23 PM
  • Hi,

    Changing the recovery model from full to simple and back again will require a full backup to be done before any log backups will complete.  You can do this by setting the ForceFull column in the management database's adm_ForceFullBackup table to True or 1 and then executing the BizTalk backup SQL Agent job afterwards.  From then on the BizTalk backup job should complete as normal.

    Wednesday, December 12, 2012 6:47 PM
  • Hi Greg.Sharp,

    This worked ! Thanks a lot for your help ! Really appreciated.

    Erik

    Wednesday, December 12, 2012 8:02 PM