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.*/