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]

SELECT DISTINCT [DatabaseName]
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
  • SSODB

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:

ALTER DATABASE BizTalkMsgBoxDb
SET recovery FULL

ALTER DATABASE BizTalkRuleEngineDb
SET recovery FULL

ALTER DATABASE SSODB
SET recovery FULL

ALTER DATABASE BAMPrimaryImport
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
Husband, father, Enterprise integration geek, Microsoft Azure MVP, Pluralsight author, Brisbane Azure User Group leader (@BrisbaneAzureUG), MCPD, MCT, MCTS & former professional musician.

2 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!

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

John Glisson - Geek of the Cloth

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

prashantbiztalkblogs

My BizTalk Experiences

The CRUCIBLE

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

paulbouwer.com

life and technology

Abdul Rafay's BizTalk Blog

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

Mike Diiorio

Connected Systems and other thoughts

BizTalk musings

Issues, patterns and useful tips for BizTalk development

EAI Guy.net

Enterprise Applicaiton Integration and SOA 2.0

Connected Pawns

Mainly BizTalk & Little Chess

Man Vs. Machine

Why can't we all just get along?

Adventures inside the Message Box

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

Biz(Talk)2

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, WCF, and Enterprise Integration

The WordPress.com Blog

The latest news on WordPress.com and the WordPress community.

%d bloggers like this: