Configure SQL Agent Jobs

If I am called to a customer already running BizTalk Server and complaining about storage and performance problems I check the SQL Agent jobs. Strange but true, in many cases the jobs are not configured and started - typical novice mistake.
Here are my basic settings I use for configuration. Just copy and modify the settings for your environment

Configure the Backup BizTalk Server Job (more info from Microsoft):


/*+++++++++++++ STEP 1: Set Compression Option +++++++++++++++++++++++*/
 
exec [dbo].[sp_SetBackupCompression] 
@bCompression = 0   /*0 - Do not use Compression, 1 - Use Compression */
 
/*+++++++++++++ STEP 2: BackupFull (performs full database backups of the BizTalk Server databases) +++++++++++++++++++++++*/
 
exec [dbo].[sp_BackupAllFull_Schedule]
'd'                 /* Frequency. Values: d (daily), h (hourly), w (weekly), m (monthly), or y (yearly).*/
,'BTS'              /* Name. Used as part of the backup file name */
,'C:\BT_BCKUP'      /* location of backup files */
,1                  /* auto full backup after failure */
,20                 /* local time hour for the full backup process to run */
 
/*+++++++++++++ STEP 3: MarkAndBackupLog (backs up the BizTalk Server database logs) +++++++++++++++++++++++*/
 
exec [dbo].[sp_MarkAll] 
'BTS'               /* Log mark name */
,'C:\BT_BCKUP'      /* location of backup files */
,1                  /* use local time */
 
/*+++++++++++++ STEP 4: Clear Backup History (specifies for how long the backup history is kept) +++++++++++++++++++++++*/
 
exec [dbo].[sp_DeleteBackupHistory] 
@DaysToKeep=14      /*The number of days you want to keep the backup history*/
        

Configure the DTA Purge and Archive Job (more info from Microsoft):


 /*+++++++++++++ STEP 1: Archive and Purge +++++++++++++++++++++++*/
 
exec dtasp_BackupAndPurgeTrackingDatabase
0,              /*@nLiveHours, Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data.*/
1,              /*@nLiveDays, Any completed instance older than the (live hours) + (live days) will be deleted along with all associated data.*/
30,             /*@nHardDeleteDays, All data (even if incomplete) older than this will be deleted.*/
'C:\BT_BCKUP',  /*@nvcFolder, Folder in which to put the backup files.*/
null,           /*@nvcValidatingServer, Server on which validation will be done. NULL value indicates no validation is being done.*/
0               /*@fForceBackup This is reserved for future use.*/       
        
inSyca would like to offer you the best possible service. We use so-called cookies to ensure that you are able to make optimum use of the website. By continuing to use this website, you agree to the use of cookies