Tuesday, February 5, 2008

BizTalk SQL JOBS

Guys,
You would have been playing around BizTalk for a long time. If you have been staying close to the Production environment you would get a chance to see the real time issues of the SQL agent jobs that are used in BizTalk.

Me as a functional consultant in BizTalk have seen some interesting scenarios happening in our Production environment wherein our processing came to a halt. Do you believe if this is caused by these agent jobs? Yes it is :-)

I will give you a fair analysis of what I have done to understand these jobs. I have bundled it together with what i got from the internet and from my friends.

BizTalk Server 2006 includes the following SQL Server Agent jobs to assist you in managing the BizTalk Server databases:

SQL Agent should be running at all times.

The following is the list of Jobs that are used by BizTalk for internal processing.

1. Backup BizTalk Server - LogShipping
2. CleanupBTFExpiredEntriesJob_BizTalkMgmtDb
3. DTA Purge (BizTalkDTADb)
4. MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb
5. MessageBox_Message_Cleanup_BizTalkMsgBoxDb
6. MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb
7. MessageBox_Parts_Cleanup_BizTalkMsgBoxDb
8. MessageBox_UpdateStats_BizTalkMsgBoxDb
9. Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb
10. PurgeSubscriptionsJob_BizTalkMsgBoxDb
11. Rules_Database_Cleanup_BizTalkRuleEngineDb
12. TrackedMessages_Copy_BizTalkMsgBoxDb


JOB: Backup BizTalk Server - LogShipping

Criticality: Very High. Estimate 1-2 days of continuous outage will take down system as disk space will run out.

Functional description and purpose.
The most important job in the environment! This job performs full database and log backups of the BizTalk Server databases and ships the logs to a remote SQL server in DC1. It is important to make sure that all database backups are done transactionally so this job to goes to each database in the system and transactionally marks the log. It then takes a backup of all logs and truncates the logs [to control database growth].

You use the Backup BizTalk Server job to back up all of the databases in your BizTalk Server source system, except for some databases used by Business Activity Services (BAS) and Business Activity Monitoring (BAM). The source system is the server or group of servers that contain live data. Because the BAS and BAM databases have different backup and restore requirements, these databases are backed up and restored using other methods.

Additionally, the SQL job does not delete old backup files out of the box. You must manage those files to conserve disk space by backing them up and deleting them after new full backups have been created.

Frequency:

Occurs every 1 day(s), every 2 hour(s) between 12:00:00AM and 11:59:59 PM.

A full backup happens once a day at 12:00AM. Incrementals happen based on the schedule throughout the day every 2 hours.

Dependencies:
There is no dependency to other jobs. But the following databases are backed up by this job by default:

1. BAM Primary Import database (BAMPrimaryImport)
2. BizTalk Tracking database (BizTalkDTADB)
3. BizTalk Management database (BizTalkMgmtDB)
4. BizTalk MessageBox database (BizTalkMsgBoxDB)
5. Rule Engine database (BizTalkRuleEngineDB)
6. SSO database
7. BizTalk Base EDI database (R2 only)

Additional databases can be backed up by adding a reference in table BizTalkMgmt..adm_OtherBackupDatabases. The following have been added to the current list of backups:

1. BAM Archive Database (BAMArchive)
2. SSO database (SSODB)



Failure:
In case of any hardware failures, the backed up temporary logshipping database can be used to restore the system from last known logmark. Data that was processed during the problem occurrence will be lost. This can’t be avoided.

It is also important to note if the job fails consecutively for a long time, the BizTalk database “data files” and “log files” will continue to grow exponentially. This in turn will affect the performance of the whole BizTalk environment and eventually consume the entire disk space if not corrected immediately.

------

JOB: DTA Purge (BizTalkDTADb)

Criticality: Medium

Functional description and purpose

As BizTalk Server processes more and more data on your system, the BizTalk Tracking (BizTalkDTADb) database continues to grow in size. Unchecked growth decreases system performance and may generate errors in the Tracking Data Delivery Service (TDDS). In addition to general tracking data, tracked messages can also accumulate in the MessageBox database, causing poor disk performance.
While previous versions of BizTalk Server included sample scripts for archiving tracked messages and purging the BizTalk Tracking database, BizTalk Server 2006 automates both processes using the DTA Purge and Archive job. By archiving and purging data from the BizTalk Tracking database, you can maintain a healthy system, as well as keep your tracking data archived for future use. Because BizTalk Tracking database archives accumulate over time and consume disk space, it is a good idea to move the BizTalk Tracking database archives to secondary storage on a regular basis.

Frequency:
Occurs every 1 day(s), every 1 minute(s) between 12:00:00AM and 11:59:59 PM.
Dependencies:
There is no dependency for this job.

Failure:
As the job is scheduled to run every minute, there won’t be any problem if this fails once. But if the job keeps on running for a long time, then we have an issue. The BizTalkDTADB database file size will grow eventually, adding it to the load for the job. This in turn will affect the performance of the whole BizTalk environment.


--------

JOB: MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb

Criticality: High

Functional description and purpose
This job detects when a BizTalk Server host instance (NT service) has stopped and releases all work that was being done by that host instance so that it can be worked on by another host instance.

Frequency:
Occurs every 1 day(s), every 1 minute(s) between 12:00:00AM and 11:59:59 PM.

Dependencies:
There is no dependency for this job.

Failure:
Consider the case if ReceiveHost on machine_xx has been stopped due to some problems. Automatically all the messages that was previously handled by this instance would be transferred to machine_xy. If there were a problem with this job, then all the messages that were received by machine_xx would not be processed until the job is running again to re-assign the work.

-----

JOB: MessageBox_Message_Cleanup_BizTalkMsgBoxDb

Criticality: Very High. Estimate 1 day of continuous outage will have significant performance impact on system.


Functional description and purpose

Do not manually start this job.


This job removes all messages that are no longer being referenced by any subscribers in the BizTalk MessageBox (BizTalkMsgBoxDb) database tables.

This is an unscheduled job, which is started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job.
Whenever a message is published in the BizTalkMsgBoxDB, a ref count is calculated. This would be the sum of all matching subscribers for this message. Whenever a subscriber subscribes a message, 1 is deducted from ref count. Finally when the ref count reaches 0 that message should be removed from the Message Box. This work is handled by this job.

Frequency:
This job is not scheduled. This job stems out from MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb. Both the jobs are responsible for cleaning up the messages in the MessageBoxDB.

Dependencies:
This job depends on the job “MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb”.

Failure:
As this job is run by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job, we actually don’t get any problems. But consider the case, if our environment is receiving more load when compared to the amount that it can process. In that case, the job would take some time to clean up the messages. As it takes a long time, more messages adds up to the load, making it to execute for a long time. This will have an impact of environment causing it to slow down the message that is received. The key point is that over a period of time, the MessageBoxDB increases in size and will make the environment to throttle.

------

JOB: MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb

Criticality: Very High. Estimate 1 day of continuous outage will have significant performance impact on system.



Functional description and purpose

This job manages the reference count logs for messages and determines when any subscriber no longer references a message. Even thought this SQL Server Agent job is scheduled to run once per minute, the stored procedure that is called by this job contains logic to ensure that the stored procedure runs continually. This is by design behavior and should not be modified.

Frequency:
Occurs every 1 day(s), every 1 minute(s) between 12:00:00AM and 11:59:59 PM.
This will start the MessageBox_Message_Cleanup_BizTalkMsgBoxDb job. If that job completes, then this job will fail. But as per the design, even after its failure, it will automatically start. So it will resemble like a long running job (But actually there is no issue in that).

Dependencies:
This job depends on the job “MessageBox_Message_Cleanup_BizTalkMsgBoxDb”.

Failure:
Failure of this job doesn’t affect the environment. But it is actually the cleanup job that is more important. As previously mentioned the cleanup should complete as soon as possible, other wise we are gonna have a worse problem ahead.

-----

JOB: MessageBox_Parts_Cleanup_BizTalkMsgBoxDb

Criticality: Medium

Functional description and purpose

This job removes all message parts that are no longer being referenced by any messages in the BizTalk MessageBox (BizTalkMsgBoxDb) database tables. All messages are made up of one or more message parts, which contain the actual message data.

Frequency:
Occurs every 1 day(s), every 1 minute(s) between 12:00:00AM and 11:59:59 PM.

Dependencies:
There is no dependency on other jobs.

Failure:
As each message contains one or more message parts, they have to be removed when any subscribers no longer reference them. This job calls a stored procedure that truncates tables PartRefCountLog1 and PartRefCountLog2. The Stored procedure places a exclusive lock on both these tables. If there are any shared locks on these tables when this job is running, it would not be able to truncate the records, making the table record size to increase drastically. Due to heavy load, the job will take more time to complete affecting the overral performance.

Vanakkam

69 comments:

Anonymous said...

Thanks. Perhaps this is the only blog around explaining the importance of these critical SQL Server agents. We almost gave this line of investigation a miss... but for your blog.

Regards

Anonymous said...

Any Idea what is the procedure to purge BAM Data.Currently I have RTA based BAM implementaion.
BAMPrimaryImport stores the data of activities.When will this move to BAM archive.Any JOb need to be run for this?

Thanks

Anonymous said...

Please explain SFTP properly what all do we need to create a recive lcation and how to generate public key. I am trying to create SFTP but no success.
Thanks
SYD

I C U said...

You have no idea how much this blog entry just helped me. Thank you very, very much!

WR said...

In our case, the MessageBox_Message_ManagedRefCountLog_BizTalkMsgBoxDb has only one entry in the history log with a failed message. Is this normal? I tried to disable and then enable the job, but still no new logs in the history. The server is very slow and we have to restart the host instances to fast it up (but only for a while), and I don't know if this is the root cause.

Thanks

Anonymous said...

Pretty great post. I simply stumbled upon your weblog and wished
to say that I've truly enjoyed surfing around your weblog posts. In any case I'll be subscribing for your feed
and I'm hoping you write again very soon!

Also visit my web blog website link

Anonymous said...

Hi, after reading this remarkable article i am as well glad to share my knowledge here with friends.


Here is my website ... Infromtion site

Anonymous said...

Thanks for your personal marvelous posting! I definitely enjoyed reading it, you happen to be a great author.
I will remember to bookmark your blog and definitely will come back sometime soon.

I want to encourage one to continue your great job, have a nice evening!



my blog post: visit url

Anonymous said...

Hmm is anyone else experiencing problems with the images on this blog loading?
I'm trying to figure out if its a problem on my end or if it's the blog.

Any feedback would be greatly appreciated.

Review my weblog; More info

Anonymous said...

Its like you read my mind! You appear to know so
much about this, like you wrote the book in it or something.

I think that you can do with some pics to drive the message home a little bit, but other than
that, this is wonderful blog. A great read.
I will definitely be back.

Feel free to surf to my web site ... More info

Anonymous said...

I constantly emailed this weblog post page to all my associates, since if like to read
it then my friends will too.

My web blog: website

Anonymous said...

I have learn several excellent stuff here.
Certainly price bookmarking for revisiting. I wonder how much attempt you place to create one of these fantastic informative web site.


My web-site; website

Anonymous said...

I don't drop a lot of comments, but i did some searching and wound up here "BizTalk SQL JOBS". And I do have a couple of questions for you if you don't mind.
Is it only me or does it seem like some of these remarks look like they are left by brain dead
individuals? :-P And, if you are writing on additional online social sites,
I'd like to follow anything new you have to post. Would you make a list of all of your communal pages like your linkedin profile, Facebook page or twitter feed?

My web-site carbon fibre furniture London

Anonymous said...

I'm amazed, I must say. Rarely do I encounter a blog that's both
educative and amusing, and without a doubt, you have hit the nail on the head.
The issue is something that not enough men and women are speaking intelligently about.
I'm very happy that I came across this in my hunt for something regarding this.

my web page visit url

Anonymous said...

We're a group of volunteers and opening a new scheme in our community. Your site offered us with valuable info to work on. You have done an impressive job and our whole community will be grateful to you.

Here is my site Infromtion site

Anonymous said...

Very energetic post, I enjoyed that bit. Will there be a part 2?


Feel free to surf to my webpage; Infromtion site

Anonymous said...

If you wish for to grow your know-how simply keep visiting this web page
and be updated with the hottest information posted here.


Feel free to visit my web-site; Website

Anonymous said...

Simply wish to say your article is as surprising. The clarity in your post
is simply excellent and i could assume you are an
expert on this subject. Fine with your permission let me
to grab your RSS feed to keep up to date with forthcoming post.
Thanks a million and please keep up the gratifying work.


Take a look at my web blog website link

Anonymous said...

No matter if some one searches for his vital thing, so he/she wants to be available that in detail, so that thing is maintained over here.


my weblog large format digital printing South Africa

Anonymous said...

Hey there, You've done a fantastic job. I'll certainly digg it and personally
recommend to my friends. I am confident they'll be benefited from this site.

Here is my web blog: hogroasts Norfolk

Anonymous said...

Woah! I'm really enjoying the template/theme of this site. It's simple,
yet effective. A lot of times it's hard to get that "perfect balance" between usability and appearance. I must say you've done a fantastic job with this.
Additionally, the blog loads super fast for me on Chrome.
Excellent Blog!

Here is my web page - Printing Gauteng

Anonymous said...

Have you ever thought about including a little bit more than just your articles?
I mean, what you say is important and everything. Nevertheless
imagine if you added some great pictures or video clips to give your posts more, "pop"!

Your content is excellent but with pics and video clips,
this website could certainly be one of the greatest in its field.
Very good blog!

My page jumping castles for sale Gauteng

Anonymous said...

If some one needs expert view about blogging then i suggest
him/her to pay a quick visit this blog, Keep up the fastidious job.


Feel free to surf to my blog post Visit website

Anonymous said...

For most recent information you have to visit the web and
on internet I found this site as a best website for most up-to-date updates.


Also visit my page ... More info

Anonymous said...

What's up, after reading this awesome post i am also delighted to share my knowledge here with mates.

Look at my site ... signage Northern Suburbs

Anonymous said...

Excellent beat ! I wish to apprentice while you amend your web site,
how can i subscribe for a blog website? The account helped me a acceptable deal.
I had been a little bit acquainted of this your broadcast offered bright clear idea

Also visit my blog post: healthy living Johannesburg

Anonymous said...

I do not even know how I ended up here, but I thought
this post was good. I do not know who you
are but definitely you're going to a famous blogger if you are not already ;) Cheers!

Have a look at my blog ... fragrances KwaZulu Natal health Care products KwaZulu Natal

Anonymous said...

I got this web page from my friend who told me concerning this web page and at the moment this time I am visiting this web page and reading very informative posts at this time.


my weblog :: portable massage beds johannesburg gym Equipment johannesburg

Anonymous said...

Hi there, I enjoy reading through your article.

I like to write a little comment to support you.


Feel free to visit my site :: life coach Johannesburg life coach Johannesburg

Anonymous said...

Hi there, all is going fine here and ofcourse every one is sharing
facts, that's genuinely excellent, keep up writing.

My web-site: website

Anonymous said...

I got this website from my buddy who shared with me regarding this site and at the moment this time I am browsing this site and reading very
informative posts at this place.

Feel free to visit my homepage ... carpets Mpumalanga

Anonymous said...

You ought to take part in a contest for one of the greatest websites on the net.
I'm going to highly recommend this blog!

Have a look at my homepage :: click Here

Anonymous said...

I needed to thank you for this wonderful read!! I certainly loved
every bit of it. I've got you book marked to look at new things you post…

Look at my web page website

Anonymous said...

Hi my family member! I wish to say that this article is
awesome, great written and include approximately all significant
infos. I'd like to look extra posts like this .

Feel free to visit my web site; property for sale

Anonymous said...

My brother suggested I might like this web site. He was once entirely right.
This publish truly made my day. You can not imagine just how a lot time I
had spent for this information! Thank you!

My weblog ... Photography in Cape Town

Anonymous said...

Wow, this piece of writing is fastidious, my sister is analyzing
these kinds of things, thus I am going to convey her.


Review my homepage: LCN Architecture

Anonymous said...

I am regular visitor, how are you everybody? This post posted
at this web site is actually nice.

Stop by my web blog; business consulting Johannesburg

Anonymous said...

This website was... how do I say it? Relevant!
! Finally I have found something which helped me. Cheers!


Check out my blog luxury villa marrakech

Anonymous said...

I am no longer sure where you are getting your info, however good
topic. I needs to spend some time studying much more or understanding more.
Thanks for magnificent info I used to be looking for this info for my mission.


my page CNC production

Anonymous said...

Unquestionably belieνe thаt whіch уou
sаid. Υouг faѵoritе reason sееmed to be on thе ωeb
thе еasiеst thing to bе aware оf.
I sаy to уou, І certainly get annoyеd ωhile
people cοnѕіdеr ωoгries that
theу plainlу do not know about.
You mаnaged tο hit the nail upon the top as wеll as dеfineԁ out
the ωhole thing withοut haѵing sіde-effects , ρeople could tаkе a sіgnal.

Will рrobаblу be bаck to get
mοrе. Thаnks

My wеb blоg :: twitter forgot password

Anonymous said...

I alwаys spent my half an hour to read this
wеbpagе's posts daily along with a mug of coffee.

my web blog - i forgot my windows password

Anonymous said...

Ιts likе you reаd mу minԁ!
Υоu seem to know a lot аbout thіs, like you wrote the
book in it oг something. I think that you could do ωith some piсs to ԁrive the meѕsage homе а little
bit, but inѕtead of that, thіs is
fantaѕtic blog. A great гeаd.
I ωill definitely be back.

Also visit my web ѕite free password manager reviews

Anonymous said...

Wonԁerful bеat ! ӏ wiѕh to apprentice whіle
you amend уour site, hoω сan i ѕubscгibe
for a blog website? The aсcοunt helped me
a accеptablе dеаl. I hаd bееn
tiny bit acquaіnted of this yоur brοadcaѕt proνіded bright clear conceρt

Vіsit my blοg :: windows xp auto login registry

Anonymous said...

Ηeуa i am fоr the first timе here.
I fоund this boaгd and I finԁ Ιt truly usеful & it helpeԁ mе οut a lot.

I hope to givе something bаck
аnd aid others like you hеlped me.

My web pаge ... login password recovery

Anonymous said...

Good infοrmation. Lucky me I came acгoss your sіte by chance (stumbleupοn).
I've bookmarked it for later!

Here is my web-site ... free password manager software

Anonymous said...

Ӏnformative article, exаctly what I needeԁ.


Fеel freе to ѕuгf to my homepage :: xp password recovery

Anonymous said...

I uѕed tо be able to finԁ gooԁ aԁviсe from youг blog posts.


Ηere is my page: gmail.com forgot password

Anonymous said...

I uѕed to be able to find gοod advice from yοur blog posts.


Take а lοoκ at my weblog - gmail.com forgot password

Anonymous said...

Еveгyone loves it ωhеn
іnԁivіduals gеt together anԁ share thoughtѕ.
Grеat ωebsіte, ѕtick with it!


My blοg post - google password recovery

Anonymous said...

The othеr day, whіlе I was at ωork, my cousin stole mу iphоne аnd tested to seе if
іt can survive a thirty foot drop, juѕt sο she can be а youtube senѕation.
My iPad is now destroyed and she haѕ 83 views.
I κnow this is completely οff tοpic but I had to
share it with someonе!

My wеbpage :: free online password manager

Anonymous said...

Hі! I've been following your weblog for some time now and finally got the courage to go ahead and give you a shout out from Lubbock Tx! Just wanted to say keep up the excellent work!

my web site ... xp password recovery

Anonymous said...

I was cuгiouѕ if уou ever thought of changing the
layout of yοur ωеbѕite?

Ӏts vеry well ωrіtten; І love
what yоuve got to say. But maybe you could а little morе in the
ωаy of contеnt so pеοple could conneсt with
it bеtter. Youve got an awful lοt of text fοr
only havіng 1 or 2 pictures. Maybe yοu
сould spaсe it out bettег?

Look into my website; password reset disk

Anonymous said...

Asking questions аrе аctuаlly pleasant thing if you
are not understanԁing sоmething comρletely, excеpt this paгagraph proνіdeѕ fastidious understanding уеt.


Feel free to visit my blog post - auto login windows 7 registry

Anonymous said...

After looκing at a fеω of the blog articles on your web page,
I ѕеriouѕly likе уour technique of blogging.
I saved as а favorite it to my bookmark site list and will be chеcking back ѕоon.
Take a look at mу webѕite too and let me κnow what yоu think.


Fеel free to visit my web-site - win 7 auto login

Anonymous said...

Its like you read my mіnd! Yοu seem
to grаsp a lot about this, suсh as
yοu ωrotе thе ebooκ іn it or ѕomething.
I feеl that уou could do with some % to power the message house a little bit, however other than that, this is wonderful blog. A fantastic read. I will certainly be back.

Here is my website: youtube password reset

Anonymous said...

Excellent ωeblog here! Also уour ѕіte гatheг a
lοt up fast! What ωeb hоst aгe уou using?
Can I get your аssociаte linκ on yоur
host? I want my wеbsite loаded up as quickly as yours lol

Неre iѕ my web pаge - windows forgot password

Anonymous said...

Wonderful post! We are linking to this great contеnt οn our website.

Κeep up the goοԁ writіng.

Also visіt mу web blog :: safe auto login

Anonymous said...

Нey! I cоuld hаve sworn I've been to this website before but after browsing through some of the post I realized it's new to me.
Anyways, I'm definitely delighted I found it and I'll be book-marking and checκing back frequently!



Reѵieω my page i forgot my email password

Anonymous said...

What a material of un-ambіguity and preѕerveneѕѕ
of valuable knowleԁgе аbоut
unехpected emotions.

Ηeге іs mу blоg ροst; password manager for android

Anonymous said...

Magnifiсent website. Plentу of helpful
informatіon here. I'm sending it to several pals ans also sharing in delicious. And obviously, thanks on your effort!

Stop by my page :: windows password recovery tool

Anonymous said...

I every tіmе spent my half an houг
to reаd this blog's content daily along with a mug of coffee.

Also visit my web-site - windows password recovery

Anonymous said...

Greetingѕ, I think your wеb site cοuld be
hаvіng wеb brοwseг compаtibility рroblems.

When I look at your web site in Safaгi, it looks fine
however, if opening іn I.Е.,
it's got some overlapping issues. I simply wanted to provide you with a quick heads up! Other than that, excellent site!

Take a look at my web page forgot laptop password

Anonymous said...

I absolutely love your site.. Very nice colors & theme.

Did you develop this site yourself? Please reply back as I'm hoping to create my own site and would love to learn where you got this from or just what the theme is called. Appreciate it!

Here is my page ... Visit here

Anonymous said...

Thanks for sharing your thoughts on raw foods.

Regards

Check out my web-site :: Click here

Anonymous said...

Piece of writing writing is also a excitement, if you be
acquainted with then you can write if not it is difficult to
write.

My page ... More information

Anonymous said...

Greetings, I think your blog could possibly be having browser compatibility problems.
Whenever I take a look at your site in Safari, it looks fine but when opening in
IE, it's got some overlapping issues. I merely wanted to provide you with a quick heads up! Apart from that, wonderful site!

my web page - visit this site

Anonymous said...

I read this article completely on the topic of
the resemblance of hottest and preceding technologies, it's awesome article.

my page - Visit here

Anonymous said...

It's remarkable designed for me to have a website, which is good for my experience. thanks admin

Stop by my page :: click this site

menna said...

شركة تنظيف موكيت بالبخار بالعين
شركة تنظيف سجاد بالبخار بالعين
شركة تنظيف كنب بالبخار بالعين
شركة تنظيف ستائر بالبخار بالعين