Posts Tagged ‘SQL Jobs’

One of the principal needs for BizTalk Administrators is the ability to monitor the health of BizTalk environments on a regular basis and react promptly to solve any possible issues that may appear in order to keep your BizTalk Server applications accessible to your users/organization.

When monitoring BizTalk Server, keep these points in mind:

  • Your infrastructure could be healthy, but your applications might not be (for example, they are receiving invalid messages and are unable to process them).
  • Your infrastructure could be unhealthy, but your applications might be running fine (for example, if a server is down, but there are enough servers assigned to the host to take over the load).
  • An infrastructure problem could surface as an application problem (for example, messages are not being processed fast enough because a server is down).

Source: Monitoring BizTalk Server

You can accomplished this by using certain tools such as: BizTalk Administration Console; BizTalk360; SCOM and many more… However, unfortunately many times, some of these tools are not available for us but we still need to accomplish this task.

Since BizTalk Server 2010 the product brings a job (Monitor BizTalk Server) that monitors the health of your environment identifying any known issues:

  • Messages without any references
  • Messages without reference counts
  • Messages with reference count less than 0
  • Message references without spool rows
  • Message references without instances
  • Instance state without instances
  • Instance subscriptions without corresponding instances
  • Orphaned DTA service instances
  • Orphaned DTA service instance exceptions
  • TDDS is not running on any host instance when global tracking option is enabled.

However this is not enough…

So how can PowerShell help us?

In some of my previous post:

I demonstrated how we could use PowerShell to monitor some aspects or features of your BizTalk environment including a script to monitor BizTalk Jobs, however this script will only monitor if the last execution of the job was successfully or not and if not send a notification.

Although this is a useful, at least in my opinion, in the last two month I found three additional situations that are important to monitor:

  • Some of the jobs were disable
  • MessageBox_Message_Cleanup_BizTalkMsgBoxDb was enable
  • SQL Server Agent was running, BizTalk Jobs were correctly enable… but despite all that no job was running (the last execution has been 7 days back – coinciding with an intervention in the system)

So I decided to create another PowerShell script. With this script you can be able to monitoring SQL Agent Jobs in your BizTalk environment using PowerShell, checking:

  • If the jobs are being executed according to the schedulers that are configured
  • If all jobs (with the exception of MessageBox_Message_Cleanup_BizTalkMsgBoxDb) are enable
  • If MessageBox_Message_Cleanup_BizTalkMsgBoxDb is disable

This script allows you to set:

  • The Jobs execution timeframe and configurations
WITH MostRecentSched AS
 (
 -- For each job get the most recent scheduled run date (this will be the one where Rnk=1)
 SELECT job_id,
		last_executed_step_date,
		RANK() OVER (PARTITION BY job_id ORDER BY last_executed_step_date DESC) AS Rnk
 FROM sysjobactivity
 )
 select name [Job Name],
	last_executed_step_date [Last Scheduled Run Date],
        DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) AS [Minutes Delayed],
		CASE WHEN enabled=1 THEN 'Enabled'
          ELSE 'Disabled'
        END [Job Status]
from MostRecentSched MRS
JOIN   sysjobs SJ
ON     MRS.job_id=SJ.job_id
where Rnk=1
and ((
		((name = 'Backup BizTalk Server (BizTalkMgmtDb)' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 17))
		OR (name = 'CleanupBTFExpiredEntriesJob_BizTalkMgmtDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 722)
		OR (name = 'Monitor BizTalk Server (BizTalkMgmtDb)' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 11520)
		OR (name = 'Rules_Database_Cleanup_BizTalkRuleEngineDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 62)
		OR (name = 'MessageBox_UpdateStats_BizTalkMsgBoxDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 7)
		OR (name IN ('DTA Purge and Archive (BizTalkDTADb)','MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb', 'MessageBox_Parts_Cleanup_BizTalkMsgBoxDb', 'Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb', 'PurgeSubscriptionsJob_BizTalkMsgBoxDb', 'TrackedMessages_Copy_BizTalkMsgBoxDb') and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 1)
	) OR (name IN ('Backup BizTalk Server (BizTalkMgmtDb)','CleanupBTFExpiredEntriesJob_BizTalkMgmtDb',
	               'DTA Purge and Archive (BizTalkDTADb)', 'MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb',
				   'MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb', 'MessageBox_Parts_Cleanup_BizTalkMsgBoxDb',
				   'MessageBox_UpdateStats_BizTalkMsgBoxDb', 'Monitor BizTalk Server (BizTalkMgmtDb)',
				   'Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb',
				   'PurgeSubscriptionsJob_BizTalkMsgBoxDb', 'Rules_Database_Cleanup_BizTalkRuleEngineDb',
				   'TrackedMessages_Copy_BizTalkMsgBoxDb') AND SJ.enabled = 0)
	  OR (name = 'MessageBox_Message_Cleanup_BizTalkMsgBoxDb' AND SJ.enabled = 1)
	)
order by name, last_executed_step_date desc;
  • And configure your email notification settings
#Set mail variables
[STRING]$PSEmailServer = "smtp"
[STRING]$Subject = "BizTalk Job Monitor"
[STRING]$From = "biztalk@monitor.com"
[array]$EmailTo = ("support@biztalk.com")

if($count -gt 0)
{
    #Send mail
    foreach ($to in $EmailTo)
    {
        $Body = $HTMLmessage
        $SMTPClient = New-Object Net.Mail.SmtpClient($PSEmailServer)
        $message = New-Object Net.Mail.MailMessage($from, $to, $Subject, $Body)
        $message.IsBodyHtml = $true;
        $SMTPClient.Send($message)
    }
}

Report sample:

BizTalk-SQL-Jobs-another-way-monitorNote: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them.

Credits: Special thanks to José Dias who helped me developing this SQL Script.

The script can be found and download on Microsoft TechNet Gallery:
Another way to monitor BizTalk SQL Agent Jobs with PowerShell (2.8 KB)
Microsoft TechNet Gallery

This next posts will focus on installing and configuring BizTalk Server 2013 R2 and additional components

Make sure that you have installed all the latest critical Windows updates from Microsoft and all the prerequisites before you install BizTalk Server 2013 R2.

Configure BizTalk Server SQL Jobs

BizTalk Server databases and their health are very important for a successful BizTalk Server database messaging environment. Although there can be many settings that we can configure, like auto-growth settings for BizTalk Databases (you can learn more here), there are two main things that we must understand and be aware, especially the database administrators:

  • Execution of the BizTalk Server SQL Agent jobs are crucial for managing the BizTalk Server databases and for maintaining optimal performance.
  • The Backup BizTalk Server job is the only supported method to backup the BizTalk Server databases and requires that all of the BizTalk Server databases are configured to use the SQL Server full recovery model.

BizTalk is shipped out with a total of 13 SQL Agent jobs. By default, the following BizTalk jobs aren’t configured and enabled upon installation:

  • Backup BizTalk Server (BizTalkMgmtDb)
  • DTA Purge and Archive (BizTalkDTADb)
  • MessageBox_Message_Cleanup_BizTalkMsgBoxDb

The two jobs that needs configuration are the two most important jobs: the “Backup BizTalk Server” and the “DTA Purge and Archive“. If you want these functionalities you must configure and enabled them.

148-BizTalk-Server-2013-R2-sql-server-agent-jobs

How to configure Backup BizTalk Server (BizTalkMgmtDb)

This is the job provided by Microsoft to do a best practice backup of the BizTalk databases. This job has to be configured for it to be able to run.

This Job consists of four steps:

  • Step 1 – Set Compression Option – Enable or disable compression during backup.
  • Step 2 – BackupFull – Performs full database backups of the BizTalk Server databases.
  • Step 3 – MarkAndBackUpLog – Backs up the BizTalk Server database logs.
  • Step 4 – Clear Backup History – Specifies for how long the backup history is kept.

To configure the Backup BizTalk Server job:

  • Press the “Windows key” to switch to the Start screen, type “SQL Management” or “SQL” and click in “SQL Server Management Studio” option to switch to the Start screen.
  • In Object Explorer panel, connect to the SQL Server instance and expand the server tree.
    • Expand the “SQL Server Agent” node
    • Expand “Jobs” node
  • Double click “Backup BizTalk Server (BizTalkMgmtDb)” to open the job properties window.
  • In the Job Properties – Backup BizTalk Server (BizTalkMgmtDb) dialog box, under “Select a page”, click “Steps”.
  • In the “Job step list”, click in the job you want to edit, and then click “Edit”

149-BizTalk-Server-2013-R2-job-properties-backup-biztalk-server-biztalkmgmtdb

Step 1 – Set Compression Option

Since BizTalk Server 2010, BizTalk Server supports compression for its SQL Server database backups thereby providing some benefits like: less space needed for the backup files, fewer I/O operations for the backup and restore and so on.

This job step calls a stored procedure named sp_SetBackupCompression on the BizTalk management database (BizTalkMgmtDb by default) to set the value on the adm_BackupSettings table.

The original script is:

exec [dbo].[sp_SetBackupCompression] @bCompression = 0 /*0 - Do not use Compression, 1 - Use Compression */

The stored procedure has only one parameter:

  • @bCompression: By default, Set Compression Option is set to 0, which makes backup compression off by default. To change the default, change Set Compression Option to 1.

Change the script to:

exec [dbo].[sp_SetBackupCompression] @bCompression = 1 /*0 - Do not use Compression, 1 - Use Compression */
Step 2 – BackupFull

The BackupFull step is responsible for performing a full backup of the database.

The original script is:

exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '<destination path>' /* location of backup files */

Where:

  • Frequency: The default is d (daily). This is the recommended setting. Other values include h (hourly), w (weekly), m (monthly), or y (yearly).
  • Name: The default is BTS. The name is used as part of the backup file name.
  • Location of backup files: Replace ‘<destination path>’ with the full path (the path must include the single quotes) to the computer and folder where you want to back up the BizTalk Server databases.

There are also three optional parameters:

  • Force full backup after partial backup failures (@ForceFullBackupAfterPartialSetFailure): The default is 0 when not specified, which means that if a log backup fails, no full backups are done until the next full backup frequency interval is reached.
    • Replace with 1 if you want a full backup to be made whenever a log backup failure occurs.
  • Local time hour for the backup process to run (@BackupHour): The default is NULL when not specified, which means that backup job will not be associated with the time zone of the BizTalk Server computer and will run at midnight UTC time (0000).
    • If you want to backup to run at a particular hour in the time zone of the BizTalk Server computer, specify an integer value from 0 (midnight) to 23 (11 PM) as the local time hour for the BackupHour parameter.
  • Use local time (@UseLocalTime): This is an extra parameter that you can also add that tells the procedure to use local time. The default value is 0.
    • If set to 0, then it uses current UTC time – GETUTCDATE() – 2007-05-04 01:34:11.933
    • If set to 1, then it uses local time – GETDATE() – 2007-05-03 18:34:11.933

Change the script to:

exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '<your_destination_path>\BizTalk Database\Full' /* location of backup files */
Step 3 – MarkAndBackUpLog

The MarkAndBackupLog step is responsible for marking the logs for backup, and then backing them up.

The original script is:

exec [dbo].[sp_MarkAll] 'BTS' /*  Log mark name */, '<destination path>' /* location of backup files */

Where:

  • @MarkName: Log mark name is part of the naming convention for backup files:
    • <Server Name>_<Database Name>_Log_< Log Mark Name >_<Timestamp>
  • @BackupPath: You must change the destination path this to a valid one. It may be local or a UNC path to another server.

There is also one optional parameter:

  • @UseLocalTime: This is an extra parameter that you can also add that tells the procedure to use local time
    • exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */,'<destination path>'  /*location of backup files */ , 1
      

Change the script to:

exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, '< your_destination_path>\BizTalk Database\Logs' /* location of backup files */
Step 4 – Clear Backup History

The Clear Backup History step is responsible for perform a cleanup of the backup history according for how long a backup should be kept.

The original script clear out the instances in the MarkLog table older than 14 days:

exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14

Where:

  • @DaysToKeep: specifies how long the backup history is kept in the Adm_BackupHistory table. Periodically clearing the backup history helps to maintain the Adm_BackupHistory table at an appropriate size.
    • The default value for the DaysToKeep parameter is 14 days.

There is also one optional parameter:

  • @UseLocalTime: This is an extra parameter that you can also add that tells the procedure to use local time
    • exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14 , @UseLocalTime =1
      

In this particular case I like to leave the default settings.

Important Note: This job step does not provide functionality for deleting backup files that have accumulated over time. You can solve this problem by implementing you custom sp_DeleteBackupHistory or by creating a Maintenance Plan to delete BizTalk Database Backups files (see more details in BizTalk Server 2013 R2: Installation and Configuration – Optimize the BizTalk Server 2013 R2 environment (Part 17))

After properly configure, to enable the Backup BizTalk Server job:

  • Under “Jobs” in SQL Server Management Studio Object Explorer, Right click in the name of the job “Backup BizTalk Server (BizTalkMgmtDb)” and select “Enable” option.

150--BizTalk-Server-2013-R2-enable-backup-biztalk-server-biztalkmgmtdb

  • In the result screen, click “Close”.

151-BizTalk-Server-2013-R2-enable-backup-biztalk-server-biztalkmgmtdb-result-screen

How to configure DTA Purge and Archive (BizTalkDTADb)

This job automates the archiving of tracked messages and the purging of the BizTalk Tracking database to maintain a healthy system and to keep the tracking data archived for future use.

And it’s configured to call the stored procedure dtasp_BackupAndPurgeTrackingDatabase, which uses the six parameters you must configure in this job:

  • @nLiveHours: Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. Default is 0 hours.
  • @nLiveDays: Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data. Default interval is 1 day.
  • @nHardDeleteDays: All data (even if incomplete) older than this will be deleted. The time interval specified for HardDeleteDays should be greater than the live window of data. The live window of data is the interval of time for which you want to maintain tracking data in the BizTalk Tracking (BizTalkDTADb) database. Anything older than this interval is eligible to be archived at the next archive and then purged. Default is 30 days.
  • @nvcFolder: Folder in which to put the backup files.
  • @nvcValidatingServer: Server on which validation will be done. NULL value indicates no validation is being done. Default is NULL.
  • @fForceBackup: Default is 0. This is reserved for future use.

To configure the DTA Purge and Archive job:

  • Start by running the “SQL Server Management Studio”, if it is not already open, press the “Windows key” to switch to the Start screen, type “SQL Management” or “SQL” and click in “SQL Server Management Studio” option from the Search menu.
  • In Object Explorer panel, connect to the SQL Server instance and expand the server tree.
    • Expand the “SQL Server Agent” node
    • Expand “Jobs” node
  • Double click “DTA Purge and Archive (BizTalkDTADb)” to open the job properties window.
  • In the Job Properties – DTA Purge and Archive (BizTalkDTADb) dialog box, under “Select a page”, click “Steps”.
  • In the “Job step list”, click Archive and Purge, and then click Edit.

152-BizTalk-Server-2013-R2-job-properties-dta-purge-and-archive-biztalkdtadb

The original script after installing BizTalk looks like this:

exec dtasp_BackupAndPurgeTrackingDatabase
0, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days
1, --@nLiveDays tinyint = 0, --will be deleted along with all associated data
30, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
null, --@nvcFolder nvarchar(1024) = null, --folder for backup files
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0 --

This means that:

  • Any completed instance that is older than the live days plus live hours will be deleted, as will any associated data.
  • Any data older than the HardDeleteDays will be deleted.

Normally I use these configurations for production environments:

exec dtasp_BackupAndPurgeTrackingDatabase 0, 10, 20, '<destination path>', null, 0

However in a development machine we don’t need to maintain the archived tracking data, so I just purge it periodically. BizTalk gives you the option to Archive and Purge the tracking data or just simple purge the data without archiving:

  • Change the SQL Statement inside “DTA Purge and Achieve” SQL Job to
declare @dtLastBackup datetime set @dtLastBackup = GetUTCDate() exec dtasp_PurgeTrackingDatabase 1, 0, 7, @dtLastBackup

After properly configure, to enable the Backup BizTalk Server job:

  • Under “Jobs” in SQL Server Management Studio Object Explorer, Right click in the name of the job “DTA Purge and Archive (BizTalkDTADb)” and select “Enable” option.
  • In the result screen, click “Close”.
MessageBox_Message_Cleanup_BizTalkMsgBoxDb

This job removes all messages that are not referenced by any subscribers in the BizTalkMsgBoxDb database tables.

Important Note: This job is also started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job. Therefore, we recommend that you disable this job.

Related links

Almost three weeks ago in my last post I release a SQL script to manager and delete the unnecessary records in the BizTalk “MarkLog” tables according to some of the best practices (you can read more here: Managing and cleaning BizTalk Server MarkLog database tables according to some Best Practices), however I knew in advance that the script had some limitations:

  • Basically the user needs to update the script manually after “installing” it. He/she needs to add and remove the databases that might not be in use (like BAM).
  • Or add, if more than one MsgBox is used.

But the beautiful of the BizTalk Community is that sometimes they provided awesome feedback, and that was exactly what happened in this case. 10 days after I published my post, I received an email from Mikael Sand, Mikael is a strong BizTalk Community member from Sweden that I had the pleasure of meeting him in person, I think the first time was two years ago in Norway, describing this exact limitations but… you are making a mistake if you think that he only described the limitations!!

Indeed he spent some time describing the limitations but I also provided me with an updated scripts with all the improvement necessary to suppress these limitations! Basically Mikael updated the script using an identical logic from another SP in the backup job so that user doesn’t need to update the script once it is installed. Also, it does not need to be updated if a database (like BAM) is added later or if you are using more than one MsgBox.

Basically the differences between the first release and this one is that:

  • Instead of hard coding implement all the delete necessary queries
/****** Delete MarkLog History from BAMAlertsApplication database ******/
DELETE FROM [BAMAlertsApplication].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BAMArchive database* *****/
DELETE FROM [BAMArchive].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BAMPrimaryImport database ******/
DELETE FROM [BAMPrimaryImport].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkDTADb database ******/
DELETE FROM [BizTalkDTADb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkMgmtDb database ******/
DELETE FROM [BizTalkMgmtDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkMsgBoxDb database ******/
DELETE FROM [BizTalkMsgBoxDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkRuleEngineDb database ******/
DELETE FROM [BizTalkRuleEngineDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from SSODB database ******/
DELETE FROM [SSODB].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep
  • Mikael have implementing this same operation dynamically by “stolen” the cursors logic and realservername from the BizTalk procedure “sp_MarkBTSLogs”. The cursor iterates all the databases that are backed up by BizTalk.
DECLARE @BackupServer sysname, @BackupDB sysname, @RealServerName sysname
DECLARE @tsql nvarchar(1024)
DECLARE @ret int
/* Create a cursor */
DECLARE BackupDB_Cursor insensitive cursor for
   SELECT ServerName, DatabaseName
   FROM	admv_BackupDatabases
   ORDER BY ServerName

open BackupDB_Cursor
fetch next from BackupDB_Cursor into @BackupServer, @BackupDB
WHILE (@@FETCH_STATUS = 0)
   BEGIN
      -- Get the proper server name
      EXEC @ret = sp_GetRemoteServerName @ServerName = @BackupServer, @DatabaseName = @BackupDB, @RemoteServerName = @RealServerName OUTPUT

      /* Create the delete statement */
      select @tsql =
      'DELETE FROM [' + @RealServerName + '].[' + @BackupDB + '].[dbo].[MarkLog]
      WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),''_'',''''), GETDATE()) > ' + cast(@DaysToKeep as nvarchar(5) )
                            
      /* Execute the delete statement */
      exec (@tsql)
      /* Get the next DB. */
      fetch next from BackupDB_Cursor into @BackupServer, @BackupDB
   END
close BackupDB_Cursor
deallocate BackupDB_Cursor

Then I spent some days testing and improving a little more the script, and again by looking to the same SP: “sp_MarkBTSLogs”, I was able to implemented some kind of error handling, just in case of something fails that an exception will be raised, as you can see in this example picture bellow:

sp_DeleteBackupHistoryAndMarkLogsHistory-error-handling

Special thanks for Mikael Sand for the feedback and for this new implementation logic, and of course to the previous persons involved: Tord Glad Nordahl, Rui Romano and Pedro Sousa.

You can download the last version of the custom sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure from:

BizTalk Server: Cleaning MarkLog Tables According to Some of the Best Practices (1.2 KB)
Microsoft | TechNet Gallery

All the BizTalk database which is being backed up by the ‘Backup BizTalk Server’ job, so all databases with the exception of the BAM Star Schema database (BAMStarSchema), has one table called “MarkLog”.

These tables are holding all the transaction marks (they are actually timestamps in a string format), set to a specific database, created by the 3th step (MarkAndBackUpLog) of the ‘Backup BizTalk Server’ job. This step, MarkAndBackupLog, is responsible for marking the logs for backup, and then backing them up. So each time this step runs, by default each 15 minutes, a string is stored on that table with the following naming convention:

  • Log_<yyyy>_<MM>_<dd>_<HH>_<mm>_<ss>_<fff>

Where:

  • Log”: Constant string
  • yyyy: The year as a four-digit number.
  • MM: The month, from 01 through 12
  • dd: The day of the month, from 01 through 31.
  • HH: The hour, using a 24-hour clock from 00 to 23.
  • mm: The minute, from 00 through 59.
  • ss: The second, from 00 through 59.
  • fff: The milliseconds in a date and time value.

Again this marks are timestamps in which the Log backups were set to be made. Example:

  • BTS_2014_05_06_14_15_01_327

Unfortunately BizTalk has no out-of-the-box possibilities to clean up these tables. And the normal procedure is to run the terminator tool to clean it up. See also: Clean up the MarkLog table with Terminator

Terminator-Tool-Purge-MarkLogs

BizTalk Terminator is an excellent tool that allows for common BizTalk problems to be addressed by scripts provided by the BizTalk Team, but needs to be used carefully and by users who know what they are doing.

Although most of the times this is extremely useful tool and our friend, in this particular operation/situation using this tool it’s not really the best option for two reasons:

  • Using this tool means that we need to stop our environment, i.e., downtime in our integration platform.
  • And if we look at the description of this “PURGE Marklog table” task it says that this operation calls a SQL script that cleans up everything in Marklog table – and maybe this is not a best practices!

I spoke to my dear friend, and one of the best BizTalk Administrator that I know, Tord Glad Nordahl to discuss and know what should be the best practice to maintain these tables:

  • Is these information (timestamps) useful for BizTalk Administrators?
  • Should I clean all the data inside this tables or should I maintain a history?
  • Is safe to clean this information in runtime?

Were some of the questions that we discussed.

Is these information (timestamps) useful for BizTalk Administrators? Should I clean all the data inside this tables or should I maintain a history?

For the same reason that we maintain a Backup history in the Adm_BackupHistory table controlled by the step “Clear Backup History” of the ‘Backup BizTalk Server’ job. This information is important for example to keep an eye on the backup/log shipping history records to see whether the back is working correctly and data/logs are restored correctly in the stand by environment. The information on the MarkLog tables are also useful for the BizTalk Administration team!

So as long as the MarkLog tables have the same info (data from the same dates) as the backup job days to keep you can safely delete the rest of the information.

As a best practices: you should respect the @DaysToKeep parameter that you specify in the “Clear Backup History” step of the ‘Backup BizTalk Server’ job.

And this is why that in my opinion, you shouldn’t use the Terminator tool to perform this operation!

Is safe to clean this information in runtime?

The rows in the Marklog table are not “required” and can be cleaned whenever you want as long the BizTalk Backup Job is not running.

Cleaning MarkLog Tables According to Some of the Best Practices

So the main challenger is how can we safely delete and maintain a history of all MarkLog tables according to some of the best practices described earlier?

My first approach was creating a new job that according to a scheduler would run a stored procedure to delete all the unnecessary information on that tables but I realized I could have two problems with this approach:

  • I need to be sure that BizTalk Backup Job wasn’t running performing the backups;
  • And I didn’t want to have to places to define the @DaysToKeep and I didn’t want to make unnecessary joins or additional selects

I was trying to find an approach for this operation to be carried out in an easy way and as fast as possible.

So I end up recreating sp_DeleteBackupHistory (that is configured is the Backup BizTalk Server (BizTalkMgmtDb) job in the last step) with a different name sp_DeleteBackupHistoryAndMarkLogsHistory and configure the job to run this step:

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndMarkLogsHistory] @DaysToKeep smallint = null, @UseLocalTime bit = 0
AS
 BEGIN
	set nocount on
	IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
		RETURN
	/*
		Only delete full sets
		If a set spans a day such that some items fall into the deleted group and the other don't don't delete the set

		Delete history only if history of full Backup exists at a later point of time
		why: history of full backup is used in sp_BackupAllFull_Schedule to check if full backup of databases is required or not.
		If history of full backup is not present, job will take a full backup irrespective of other options (frequency, Backup hour)
	*/

	declare @PurgeDateTime datetime
	if (@UseLocalTime = 0)
		set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE())
	else
		set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE())

	DELETE [dbo].[adm_BackupHistory]
	FROM [dbo].[adm_BackupHistory] [h1]
	WHERE 	[BackupDateTime] < @PurgeDateTime
	AND	[BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [h1].[BackupSetId] AND [h2].[BackupDateTime] >= @PurgeDateTime)
	AND EXISTS( SELECT TOP 1 1 FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] > [h1].[BackupSetId] AND [h2].[BackupType] = 'db')

	/****** Delete MarkLog History from BAMAlertsApplication database ******/
	DELETE FROM [BAMAlertsApplication].[dbo].[MarkLog]
    WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BAMArchive database* *****/
	DELETE FROM [BAMArchive].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BAMPrimaryImport database ******/
	DELETE FROM [BAMPrimaryImport].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkDTADb database ******/
	DELETE FROM [BizTalkDTADb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkMgmtDb database ******/
	DELETE FROM [BizTalkMgmtDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkMsgBoxDb database ******/
	DELETE FROM [BizTalkMsgBoxDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkRuleEngineDb database ******/
	DELETE FROM [BizTalkRuleEngineDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from SSODB database ******/
	DELETE FROM [SSODB].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep
 END

Steps required to install/configure

  • Create the sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure described earlier
  • You need to adjust the SQL Script to your environment because some environment may not have all features installed, for example: Business Rules and in this case BizTalkRuleEngineDb database will not exist.
  • Change and configure the 4th step of the ‘Backup BizTalk Server’ job – “Clear Backup History” to call this new stored procedure

sp_DeleteBackupHistoryAndMarkLogsHistory

Note: Do not change or delete the “sp_DeleteBackupHistory”!

Special thanks for Tord Glad Nordahl for reviewing and all the feedback, and to my friends at DevScope Rui Romano and Pedro Sousa for the helping me developing this SQL Query.

Hope you find it useful

You can download the custom sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure from:

BizTalk Server: Cleaning MarkLog Tables According to Some of the Best Practices (1.2 KB)
Microsoft | TechNet Gallery

 

BizTalk Server databases and their health are very important for a successful BizTalk Server database messaging environment. BizTalk is shipped out with a total of 13 SQL Agent jobs that perform important functions to keep your servers operational and healthy.

Like any other system, all BizTalk Server databases should be backed up and BizTalk Server will provide out-of-the-box a job for accomplished that: Backup BizTalk Server (BizTalkMgmtDb) job.

This job makes both Full and Log backups. By default the Backup BizTalk Server job performs a full backup once a day and performs log backups every 15 minutes. This means that once the full backup is performed you need to wait 24 hours for it to automatically do another full backup of the BizTalk Server databases… even if you try to manually run the job, it will only make the backups of the log files.

But sometimes we need, for several reasons, to have the ability and the possibility to force a full backup:

  • We will have some maintaining plan on the server, or apply a new configuration, and we want to backup the environment
  • Or simple we will install a new integration application and again we want to have a backup in this exact moment

Each company have is policies, so again for several reasons, we sometimes need to force a full backup of all BizTalk Server databases.

The standard way is to use the “BizTalkMgmtDb.dbo.sp_ForceFullBackup” stored procedure. However, and unlike what many people think, this stored procedure does not perform a full backup it only marks internally on BizTalk Server databases that the next time the Backup BizTalk Server job runs it will need to perform a full backup of the data and log files.

All BizTalk Admins know this (if they don’t know, they should!), however sometimes we need to really on DBA Admins to perform this task, and do not misunderstand me, I have nothing against DBA! All the BizTalk Administration team should be composed by several element with different skills: DBA Admins, network admins, Sys Admins and a BizTalk Admins… each playing its role. But sometime DBA don’t really understand the BizTalk infrastructure and BizTalk Administrator should have an important role advising and helping them (like in other situations it will be the opposite).

And because this is not a day by day task people tend to forget all the steps and sometimes they will create custom scripts to perform this task, but you really need to be aware of two important things:

  • The Backup BizTalk Server job is the only supported method for backing up the BizTalk Server databases. Use of SQL Server jobs to back up the BizTalk Server databases in a production environment is not supported.
  • You can use the SQL Server methods to backup the BizTalk Server databases only if the SQL Server service is stopped and if all BizTalk Server processes are stopped.

So to help one of my DBA teams I end up creating this job that is composed by two steps:

  • Step 1: Force Full Backup
    • That will call the “BizTalkMgmtDb.dbo.sp_ForceFullBackup” stored procedure
  • Step 2: Backup BizTalk Server
    • That will call the standard Backup BizTalk Server (BizTalkMgmtDb) job

Force-Full-Backup-BizTalk-Server-BizTalkMgmtDb

Note: Of course this custom job should be disabled and manually executed when you need it!

Hope you find it useful

You can download the custom “Force Full Backup BizTalk Server (BizTalkMgmtDb)” Job from:

Force Full Backup BizTalk Server (BizTalkMgmtDb) Job (863 bytes)
Microsoft | TechNet Gallery

 

Now I’m playing a little with this topic hehehe… but be with me, this have a final goal that I will reveal soon (probably two weeks from now since next week I will be away in the MVP Summit and BizTalk Summit in Seattle). Until then let’s try to find some of the “basic” possible errors that you can find in the Backup BizTalk Server job.

In my previous posts (here and here), I have explained two possible errors that you can find when trying to run this job, today we will talk about this one:

SQL Server Scheduled Job ‘Backup BizTalk Server (BizTalkMgmtDb)’ (0x4020953E49DEFA43B8FEC18D6AD9B062) – Status: Failed – Invoked on: 2013-11-12 08:50:46 – Message: The job failed. The Job was invoked by User BTS2013LAB01\Administrator. The last step to run was step 3 (MarkAndBackupLog). The job was requested to start at step 1 (Set Compression Option).

 

 

Backup-BizTalk-Server-job-warning-message-MarkAndBackupLog

Although the execution of job ‘Backup BizTalk Server (BizTalkMgmtDb)’ failed, no error is register in the event viewer only the warning showed above. This probably happens because the main task of this job: “BackupFull” responsible for performing a full database backups of the BizTalk Server databases, is properly executed. You can validate this in the tables “adm_BackupSettings” and “adm_BackupHistory”, or on the file system where the backup files are saved.

However, and as the message states, the job was not entirely performed and there is a problem in the third task: MarkAndBackupLog. Again the error message doesn’t help us much, so what you should do is go to the Backup BizTalk Server job history to try so see if you can obtain more information’s. In this case the log history show as much more information:

Log Job History (Backup BizTalk Server (BizTalkMgmtDb))
Step ID 3
Job Name Backup BizTalk Server (BizTalkMgmtDb)
Step Name MarkAndBackupLog
Sql Severity 15
Sql Message ID 105
Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Incorrect syntax near ‘´’. [SQLSTATE 42000] (Error 102) Unclosed quotation mark after the character string ‘ /* location of backup files */ ‘. [SQLSTATE 42000] (Error 105). The step failed.

Backup-BizTalk-Server-job-error-history-MarkAndBackupLog

 

CAUSE

The MarkAndBackupLog step is responsible for marking the logs for backup, and then backing them up. This step execute the “sp_MarkAll” stored procedure that accepts two mandatory parameters:

  • @MarkName: Log mark name is part of the naming convention for backup files
  • @BackupPath:. You must change the destination path this to a valid one. It may be local or a UNC path to another server

And one optional parameter:

  • @UseLocalTime: This is an extra parameter that you can also add that tells the procedure to use local time

The mandatory parameters (MarkName and BackupPath) must be placed between single quotation marks:

  • MarkName: 'BTS', 'BTS2013LAB01' and so on
  • BackupPath: 'c:\backups'

The problem here is that for some reason I’m not using a single quotation mark:

exec [dbo].[sp_MarkAll] ‘BTS’ /* Log mark name */, C ….’ …

SOLUTION

Fix the job to ensure that all the parameters of the sp_MarkAll is between single quotation marks.

 

Another error that you can find in the Job history is:

Log Job History (Backup BizTalk Server (BizTalkMgmtDb))
Step ID 3
Job Name Backup BizTalk Server (BizTalkMgmtDb)
Step Name MarkAndBackupLog
Sql Severity 16
Sql Message ID 3013
Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot open backup device ‘K:\Backups\BizTalk Database\Logs\BTS2013LAB01_BAMAlertsApplication_Log_BTS_2013_11_12_17_51_07_990.bak’. Operating system error 3(The system cannot find the path specified.). [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

In this case you also get one or several errors in the Event Viewer:

BackupDiskFile::CreateMedia: Backup device ‘K:\Backups\BizTalk Database\Logs\BTS2013LAB01_BAMAlertsApplication_Log_BTS_2013_11_12_17_51_07_990.bak’ failed to create. Operating system error 3(The system cannot find the path specified.).

Backup-BizTalk-Server-job-error-message-MarkAndBackupLog

CAUSE

If you check the parameters of the “sp_MarkAll” stored procedure you will see that the second parameter is the location of backup files and this location must exist in the file system.

SOLUTION

You must remember that you need to ensure that all the paths specified in the BizTalk jobs must exist in the file system. In my case, we create the folder on the file system and run the job again with success.

After writing my last post I remembered that in the past I got a related error to this problem but with a different cause and solution. So I decided to emulate the error in my lab environment and make a second part of this topic.

I remember that on occasion, in order to be faster, I copied all the job configuration text from of my blog and pasted in the BackupFull step job window. However when I try to run I got a similar error:

SQL Server Scheduled Job ‘Backup BizTalk Server (BizTalkMgmtDb)’ (0x4020953E49DEFA43B8FEC18D6AD9B062) – Status: Failed – Invoked on: 2013-11-11 17:45:00 – Message: The job failed. The Job was invoked by Schedule 9 (MarkAndBackupLogSched). The last step to run was step 2 (BackupFull).

Backup-BizTalk-Server-job-error-message-MarkAndBackupLogSched

Again the error message doesn’t help us much, so what you should do is go to the Backup BizTalk Server job history:

  • Right click in “Backup BizTalk Server (BizTalkMgmtDb)” job and select “View History” option

And try so see if you can obtain more information about the error.

Unlike the event viewer and the error that I put in the previous post, in this case the log history show as much more information:

Date 11/11/2013 17:45:00
Log Job History (Backup BizTalk Server (BizTalkMgmtDb))
Step ID 2
Job Name Backup BizTalk Server (BizTalkMgmtDb)
Step Name BackupFull
Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Incorrect syntax near ‘´’. [SQLSTATE 42000] (Error 102) Unclosed quotation mark after the character string ‘ /* location of backup files */ ‘. [SQLSTATE 42000] (Error 105). The step failed.

Backup-BizTalk-Server-job-error-history-Unclosed-quotation-mark

CAUSE

The copy of the text from my blog brought a wrong ASCII format transforming the single quotation mark character in an accent (´).

All the parameter of the sp_BackupAllFull_Schedule must be placed between single quotation marks:

  • Frequency: 'd', 'w' and so on
  • Name: 'BTS'
  • Location of backup files: 'c:\backups'

SOLUTION

Fix the job to ensure that all the parameters of the sp_BackupAllFull_Schedule is between single quotation marks.

For several years I’ve been setting up dozens of times the Backup BizTalk Server job without any kind of problems… but there’s a first time for everything and when I was trying to help remotely a friend configuring this job, we got stuck with this error when we try to start it:

Log Job History (Backup BizTalk Server (BizTalkMgmtDb))
Step ID 0
Job Name Backup BizTalk Server (BizTalkMgmtDb)
Step Name (Job outcome)
Message
The job failed. The Job was invoked by User domain\user. The last step to run was step 2 (BackupFull). The job was requested to start at step 1 (Set Compression Option).

Backup-BizTalk-Server-job-error-message

I knew that the job was configured correctly but unfortunately I didn’t have access to the SQL Server in order to see if I could find out more information about what was happening… Well, according to the error message we know that the job started in the first task “Set Compression Option” but failed in the second “BackupFull”. Beyond that the error message doesn’t help us much.

By going to the job history (right click in “Backup BizTalk Server (BizTalkMgmtDb)” job and select “View History” option)) we can validate that the problem in the BackupFull task but again the message described there doesn’t help us much.

Backup-BizTalk-Server-job-error-history

CAUSE

Unfortunately this error can happen for several reasons and we need to check what the best option for us is::

  • In Microsoft BizTalk Server 2004 and later, the Backup BizTalk Server job fails, and problems occur that relate to limited disk space. This happens when the backup files accumulate over time. This fills up the disk that houses the backup files.
    • This issue occurs because the SQL Server Agent job that is named “Backup BizTalk Server” does not provide functionality for deleting backup files that have accumulated over time. See: The “Backup BizTalk Server” job fails when backup files accumulate over time in the Microsoft BizTalk Server database server
    • Or you can create a Maintenance Plan to delete BizTalk Database Backups files see more here
    • I found also this great suggestion by Tord Glad Nordahl: “Verify that the compression option for the database is correct, after this open the management database located the table called “adm_BackupSettings” update this column “ForceFull” and set the value to 1. Then manually run the backup job again.” In this forum question.

But none of these suggestions work for me, in fact this was the first time that we were trying to run the job.

However sometimes solutions are simpler than it seems. If you check the parameters of the BackupFull step you will see that the third parameter is the location of backup files and this location must exist in the file system… so because I didn’t have access to the SQL Server machine, I asked to my friend to:

  1. Validate if the folder exist in the file system;
  2. See in the event viewer of the SQL Server if there was more information about the error;

The result was:

  1. The folder didn’t exist… never a good sign
  2. In the event viewer of the SQL Server Machine there was an entry saying the exact problem:

BackupDiskFile::CreateMedia: Backup device ‘K:\Backups\BizTalk Database\Full\BTS2013LAB01_BAMAlertsApplication_Full_BTS_2013_11_11_16_45_25_870.bak’ failed to create. Operating system error 3(The system cannot find the path specified.).

SOLUTION

You must remember that you need to ensure that all the paths specified in the BizTalk jobs must exist in the file system. In my case, we create the folder on the file system and run the job again with success.

Backup-BizTalk-Server-job-success

After I deploy a BAM activity to a new BizTalk Server 2010 environment I create a job called “BAM Generic Import data” to import all BAM data information to OLAP Cubes that will be presented on Aggregations tab in the BAM Portal.

However when I try to manual execute the job I got an error messages saying:

“The cube “MyView” was not processed. Please run the cube DTS to process the cube”

When I went to the event viewer I saw several Bam Event Provider warning messages with the following details:

Bam-Event-Provider-warning-messages

Log                Job History (BAM Generic Import data)
Step ID                0

Server                MyServer\BIZTALK
Job Name                BAM Generic Import data
Step Name                (Job outcome)
Duration                00:00:00
Sql Severity                0
Sql Message ID                0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted                0

Message

The job failed. Unable to determine if the owner (Domain\User) of job BAM Generic Import data has server access (reason: Could not obtain information about Windows NT group/user ‘Domain\User’, error code 0x2. [SQLSTATE 42000] (Error 15404)).

CAUSE

This error most likely when the machine account that runs the job (BAM Generic Import data) doesn’t have permission to query the AD.

Job-BAM-Generic-Import-Data-ower

SOLUTION 1

I would recommend requesting to the AD administrator access to this user or change owner that runs this job to a low-privileged domain account that has proper permissions on the AD – Members of the Domain should be enough.

SOLUTION 2

However in my case the AD administrator was in vacation and the other unavailable and my user account indeed didn’t have permission to query the AD (don’t ask me why) so the only solution that I found to try to solve the problem was to change the owner to the SQL Login System administrator (sa) that, lucky, wasn’t locked.

Job-BAM-Generic-Import-Data-ower-fixed

And problem solved … at least momentarily until we can apply the solution 1

One of the principal needs for BizTalk Administrators is the ability to monitor the health of BizTalk environments and react promptly to possible problems, you can accomplished this by using certain tools such as: BizTalk Administration Console; BizTalk360; SCOM and many more… However, unfortunately many times, some of these tools are not available for us but we still need to accomplish this task.

In my last post I demonstrated how we could use PowerShell to monitor disk space. In this post I will explain how you can be able to monitoring SQL Agent Jobs in your BizTalk environment using PowerShell.

So how can PowerShell help us?

Windows PowerShell is a Windows command-line shell designed especially for system administrators. It includes an interactive prompt and a scripting environment that can be used independently or in combination. PowerShell can be used by BizTalk administrators to help them in automating tasks and monitor certain resources or operations.

With this script you can be able to monitoring SQL Agent Jobs in your BizTalk environment using PowerShell. BizTalk is shipped out with a total of 12 SQL Agent jobs. 2 of these jobs must be configured. The two jobs that needs configuration are the two most important jobs. The “Backup BizTalk Server” and the “DTA Purge and Archive”.

This script allows you to set:

  • A range of machines you need to monitor
#########################################################
# List of SQL Servers to be monitored
#########################################################
$sqlservers = Get-Content ".\SQL.txt";

The SQL.txt is a simple text file with the list of all machine names you want to monitor:
servername1
servername2
servername3

  • Configure list of SQL Job that will not be subject to monitoring
#########################################################
# List of SQL Job that will not be subject to monitoring
#########################################################
$jobsToIgnore=@{
	"DTA Purge and Archive (BizTalkDTADb)" = "IGNORE"
}
  • And configure your email notification settings
#########################################################
# Preparation of sending the email
#########################################################
$msg = New-Object Net.Mail.MailMessage
if($tmp -ne $null) {
    $smtpServer="mySMTPServer"
    $smtp = New-Object Net.Mail.SmtpClient -arg $smtpServer
    $fromemail = "suport@mail.net"
    $msg.From = $fromemail
    $users = "mail1@mail.net, mail2@mail.net"
    $msg.To.Add($users)
    $msg.Subject = "SQL Job Report "
    $msg.IsBodyHTML = $true
    $msg.Body = $HTMLmessage
    $smtp.Send($msg)
}

The script will monitor the JQL Agent Jobs in all machines and you will receive an email with a list of all jobs listed that have problems and need your atention. SQL Jobs that are functioning properly will not be listed.

#########################################################
# Monitoring Process
#########################################################
$report = foreach($sqlserver in $sqlservers) {
    # Create an SMO Server object
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;  # srv vai ficar com o nome do servidor na lista sqlservers
    # total jobs and start the counters to 0.
    $totalJobCount = $srv.JobServer.Jobs.Count;
    $failedCount = 0;
    $successCount = 0;
    $Day = Get-Date ;

	$tmp = foreach($job in $srv.JobServer.Jobs)
	{
    	$colour = "Green";
        $jobName = $job.Name;
        $jobEnabled = $job.IsEnabled;
        $jobLastRunOutcome = $job.LastRunOutcome;
        $jobLastRunDate= $job.LastRunDate;
        $detail = $job.EnumHistory();
        $timerows = $job.EnumHistory().Rows

        [string]$runDetails=""
        if($job.EnumHistory().Rows[0] -ne $null){
            [string]$runDetails=""+ $job.EnumHistory().Rows[0].Message
            if($job.EnumHistory().Rows[1] -ne $null){
                [string]$runDetails+="<br><br>"+ $job.EnumHistory().Rows[1].Message
            }
        }
        else{
			[string]$runDetails="-"
		}

        $executions = foreach ($row in $timerows) {
            if(-not $row.HasErrors){
                New-Object PSObject -Property @{Duration = $row.RunDuratio}
            }
        }

	    $media =  $executions | Measure-Object -Property Duration -Average | select -expand Average
	    if($timerows -ne $null) {
	        $lastRun =  $timerows[0]['RunDuration']
	    }
		else
	    {
	        $lastRun = -1
	    }

        if($jobLastRunOutcome -eq "Failed")
        {
            #$colour = "Red";
            $failedCount += 1;
        }
        elseif ($jobLastRunOutcome -eq "Succeeded")
        {
            $successCount += 1;
        }
        New-Object PSObject -Property @{
           	Server = $sqlserver
           	JobName = $jobName
           	Enabled = $jobEnabled
           	OK = $successCount
           	Failed = $failedCount
           	LastRun = $jobLastRunOutcome
           	StartTime = $jobLastRunDate
           	Duration = $lastRun
           	ExpectedDuration = $media
           	RunDetails=$runDetails
        }

		##############################################################
		# Convert Seconds to Hours:Minutes:Seconds Table Duration    #
		##############################################################
		if ($lastRun -gt 9999) {
          	$hh = [int][Math]::Truncate($lastRun/10000);
          	$lastRun = $lastRun - ($hh*10000)
		}
		else {
		    $hh = 0
		}
	    if ($lastRun -gt 99) {
	    	$mm = [int][Math]::Truncate($lastRun/100);
	     	$lastRun = $lastRun - ($mm*100)
	    }
	    else {
	        $mm = 0
	    }
		$dur = ("{0:D2}" -f $hh) + ':' + "{0:D2}" -f $mm + ':' + "{0:D2}" -f $lastRun

		######################################################################
		# Convert Seconds to Hours:Minutes:Seconds Table ExpectedDuration    #
		######################################################################
	    [system.Double] $media
	    [Int32] $imedia = [System.Convert]::ToInt32($media);

    	if ($imedia -gt 9999) {
          	$h = [System.Double][Math]::Truncate($imedia/10000);
          	$imedia = $imedia - ($h*10000)
        }
        else {
          	$h = 0
        }
	    if ($imedia -gt 99) {
	     	$m = [System.Double][Math]::Truncate($media/100);
	     	$imedia = $imedia - ($m*100)
	    }
        else {
          	$m = 0
        }
     	$expDura = ("{0:D2}" -f $h) + ':' + "{0:D2}" -f $m + ':' + "{0:D2}" -f $imedia
	}
}

Report sample:

Note: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them.

The script can be found and download on Microsoft TechNet Gallery:
Monitoring SQL Agent Jobs in your BizTalk environment with PowerShell (7.4 KB)
Microsoft TechNet Gallery