BizTalk SQL Agent Jobs – Configuration Errors

In a previous post, I talked about how to configure the BizTalk SQL Agent jobs responsible for backing up the databases and purging the tracking data. These jobs are not configured by default, but their operation is essential for keeping your BizTalk system running smoothly.

Today, I inherited a developer VM at a new client with a warning that it was “running a bit slow…”.  Upon investigation, I discovered that the Backup BizTalk Server job had not been configured, so I promptly set about doing that task. However, I uncovered an error once I tried to run the job the first time:

Executed as user: NT AUTHORITY\SYSTEM. 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).  The step failed.

Hmmm… so the databases were not configured in the manner that Microsoft requires. Because the BizTalk backup jobs use log marking, FULL is the only supported recovery option. Okay, well this was easy enough to fix once I looked up the proper syntax.

First step was to check which databases were scheduled for backup. You can do this by querying the backup history table:

USE [BizTalkMgmtDb]

FROM [BizTalkMgmtDb].[dbo].[adm_BackupHistory]

Now if no backup has previously been performed, you might not see anything in this table. In that case, you can pretty much count on the following databases being included with any basic BizTalk installation:

  • BizTalkMgmtDb
  • BizTalkMsgBoxDb
  • BizTalkDTADb
  • BizTalkRulesEngineDb

Next step is to determine which of these databases are currently configured in SIMPLE recovery mode. That can be revealed by executing the following stored proc against each database name retrieved above:

EXEC sp_helpdb ‘SSODB’

The information you want is in the ‘status’ column:

Status=ONLINE, Updateability=READ_WRITE, …, Recovery=SIMPLE, Version=…

Now you just need to run the following commands to change the recovery mode from SIMPLE to FULL for each of the databases identified in the previous step:

SET recovery FULL

SET recovery FULL

SET recovery FULL

SET recovery FULL

Now all should be good, right? Well… maybe not yet. This is the error that I got next:

Executed as user: NT AUTHORITY\SYSTEM. BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214)  BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

What the…?  But an initial backup is exactly what I want to do. Why is it complaining that there is no previous backup? It turns out that the previous failed attempt has only performed a partial backup, whereas a full backup is required prior to performing the “MarkAndBackupLog” step. So what you need to do is force a full backup by running this command (thanks to Lex Hegt and his very helpful blog post for this info):

USE [BizTalkMgmtDb]

EXEC sp_ForceFullBackup

Now your backup job should run successfully – although the above command doesn’t actually initiate the backup, it just creates a setting in the BizTalkMgmtDb that will trigger a full backup the next time it runs. You can either wait until the next scheduled backup (which by default is every 15 minutes), or kick off one manually by:

  1. Right-clicking the Backup BizTalk Server job
  2. Selecting “Start job at step…”
  3. Clicking the Start button on the Start Job on <ServerName> window

Hopefully, you’ll soon see green checks against both steps! Smile

About Dan Toomey
Enterprise integration geek, Microsoft Azure MVP, Pluralsight author, public speaker, MCSE, MCT, MCTS & former professional musician.

3 Responses to BizTalk SQL Agent Jobs – Configuration Errors

  1. Wow! This blog looks exactly like my old one! It’s on a totally different subject
    but it has pretty much the same layout and design.
    Great choice of colors!

  2. Rémon ter Haar says:

    Thanks, this post did help me out!

  3. Rich Wallace says:

    As many times as I’ve installed and configured BTS, it’s needing to run the ForceBackup sproc that gets me…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

John Glisson - Geek of the Cloth

Thoughts on integration, technology and what-not...

Prashant BizTalk And Azure Integration Blogs

My Integration Experiences - BizTalk And Azure Integration


THINK: It's not illegal....yet.....

Abdul Rafay's BizTalk Blog

My experiences with BizTalk related to architecture, development and performance in my enterprise.

BizTalk musings

Issues, patterns and useful tips for BizTalk development


Enterprise Applicaiton Integration and SOA 2.0

Connected Pawns

Mainly BizTalk & Little Chess

Adventures inside the Message Box

BizTalk, Azure, and other tools in the Microsoft stack - Johann Cooper


Talk, talk and more talk about BizTalk

Richard Seroter's Architecture Musings

Blog Featuring Code, Thoughts, and Experiences with Software and Services

Sandro Pereira BizTalk Blog

My notes about BizTalk Server 2004, 2006, 2006 R2, 2009, 2010, 2013 and now also Windows Azure BizTalk Services.

BizTalk Events

Calendar of BizTalk events all over the world!

Mind Over Messaging

Musings on BizTalk, Azure, and Enterprise Integration News

The latest news on and the WordPress community.

%d bloggers like this: