Install the stored procedures on the Tracking database

Perform the following steps on the SQL Server that hosts the Tracking database. This should be the same server that you configured in the previous procedure.

1. Verify that Query Analyzer is open.
   
   
2. On the File menu, click Open.
   
   
3. In the Open Query File dialog box, in the Look in list, browse to \Program Files\Microsoft BizTalk Server\Setup.
   
   
4. Click BTS_Tracking_Archive_Purge_Script.sql and click Open.
   
   
5. On the Query menu, click Execute.

   The script creates a stored procedure named dta_purge_old_records. The script creates a SQL Server Agent job named "Archive and Purge BizTalk Tracking database: <Name of your Tracking database>." By default, the SQL Server Agent job is not enabled.

6. If necessary, open Enterprise Manager.
   
   
7. Expand Microsoft SQL Servers, expand SQL Server Group, expand the server that hosts the Tracking database, expand Management, expand SQL Server Agent, and then click Jobs.
   
   
8. In the details pane, click Archive and Purge BizTalk Tracking Database: <Name of your Tracking database>.
   
   
9. On the Action menu, click Properties.
   
   
10. On the Steps tab, verify that dtaStep_execute_stored_proc is selected and click Edit.
    
    
11. In the Edit Job Step dialog box, on the General tab, set the input parameters in the Command box.

    The following are some examples for configuring the input parameters passed to the dta_purge_old_records stored procedure. For more information about the syntax and arguments in this stored procedure, see Tracking Database Archive/Delete Stored Procedure Usage.

    • To delete but not archive all completely processed interchanges and documents that are older than one day, edit the job as follows:

      USE InterchangeDTA
      EXEC dta_purge_old_records 1, 24, 1,0

    • To archive and delete all interchanges regardless of whether they are completely processed, with the exception of the latest 1,000 interchanges, edit the job as follows:

      USE InterchangeDTA
      EXEC dta_purge_old_records 2, 1000, 0, 1,
       N'<Name of a SQL Server>',
       N'<Name of the archive Tracking database>'

12. Click OK.
    
    
13. On the Schedules tab, verify that BizTalk Archive and Purge Schedule is selected and click Edit.
    
    
14. In the Edit Job Schedule dialog box, click Change in the Schedule Type box.
    
    
15. In the Edit Recurring Job Schedule dialog box, change the occurrence, frequency, and duration of the SQL Server Agent job to what you want and click OK.
    
    
16. On the General tab, select the Enabled check box and click OK.

    The changes are saved and the SQL Server Agent job is enabled.

Note

• This procedure assumes that the installation folder for BizTalk Server 2002 is \Program Files\Microsoft BizTalk Server. If you installed BizTalk Server 2002 in a different folder, your folder paths might be different from the folder paths listed here.

Did you find this information useful? Please send your suggestions and comments about the documentation to BizTalk Server Documentation Feedback@microsoft.com