The purpose of Terminator

This text below is retrieved from the Terminator help files. Thanks Microsoft though!

Biztalk has been in service for several years. Many problems have been resolved by the Biztalk Product group and CSS Engineers with SQL and WMI scripts. Terminator was created to accomplish 5 primary objectives:

• A centralized repository for these scripts
• A way for users to easily execute these scripts
• A mechanism to auto-update these scripts
• MBV Integration which allows users to easily resolve common BizTalk database integrity issues identified by the BizTalk MessageBoxViewer tool (http://blogs.technet.com/jpierauc/pages/msgboxviewer.aspx).
• An automated way to improve performance on a Biztalk Group by tuning with standard tunings that are known to improve Biztalk Performance.

So that’s for a global description of the purposes of Terminator. The actions it performs can be divided in a couple of categories:

• View
• Performance
• Manage
• Delete

From each category the actions are shown below.

View category

Actually the ‘safest’ category to perform. Basically the queries in this category retrieve several counts. So these are just checks if your BizTalk databases are healthy, comparable with the queries from MessageBoxViewer. When you want to know how many records your MarkLog table has, you need to execute the script called:

               View Table Row Count (No SQL Statistics)

Performance category

In this category some actions are executed to tune your system.

Manage category

The actions in this category do several kinds of configuration of your BizTalk system.

Delete category

This category contains amongst others the action to delete (or actually purge) the records in the MarkLog table.

How to obtain Terminator

You can download Terminator from the Microsoft website:

http://www.microsoft.com/download/en/details.aspx?id=2846

Terminator comes as a ZIP file, which contains the following files:

• BTSTerminator.exe
• BTSTerminator.exe.config
• BTSCore.dll
• TerminatorHelp.htm
• A folder TerminatorHelp_files which contains several pictures which are shown in the help file.

As you will see later, the download from Terminator is only valid for a certain time. So if you’ve downloaded (and used) Terminator before and you run it now again, you might be requested to download a newer version of it.

Before you run any repairing actions in Terminator, you need to take care that the machine on which you will run Terminator, meets the following requirements:

• Powershell 2.0 (http://support.microsoft.com/kb/968929)

But when you start Terminator, accept the License Terms and navigate to the Help menu, you’ll find an option to install Powershell 2.0. This option redirects you to same website as mentioned at the bullet above.

Running Terminator for the first time

When you run Terminator for the first time you need to accept the License Terms.

After you have accepted the Terms, you get the main window. It has 3 checkboxes which are hard to ignore, but first make sure you enter the following information:

• the name of the server which contains your BizTalk Management database
• the name of your BizTalk Management database

Further notice the title bar which mentions until when you can this your copy of Terminator.

Next, make sure you did make a backup of your BizTalk databases, that all your Host Instances are stopped and that all your BizTalk related SQL Agent jobs are stopped as well. When you have done this, turn on the checkboxes and the Connect button below will become enabled.

Now click Connect. The other buttons will become enabled as well.

To delete the records in the MarkLog table, click the Delete button. The text in the dropdown box will now state:

       Select a deletion task from this drop down

Click on the dropdown box and select ‘PURGE Marklog Table’.

On the Description tab page you get some information about what this task does. As you see, this task cleans up ALL records in the MarkLog table from the selected database. It supports all BizTalk versions since BizTalk 2004.

As the task already says, it Purges the records in the table, instead of deleting them. The difference between the two, is that in case of a Purge statement less data is logged to the logfile (.ldf) from your database.

Except for the BizTalk databases which are in the Marked backup, you can also run this script on any custom databases, which are also backed up by the Backup BizTalk Server job.

IMPORTANT: The task has to be executed for every single database which has (too) many records in the MarkLog table. Consider 150.000 records as too many.

When you want to know in advance, how much data will be cleaned up, you run the task mentioned at View Task. Off course you already know that your MarkLog table has many records, otherwise you would not be here J.

Now click on the Parameter tab page, to configure the database from which you want to purge the MarkLog table.

Enter the DatabaseServerName and the DatabaseName and click the Execute button

The screen below shows the MarkLog table on my Dev BizTalk 2010 environment. It has 150.000 records (don’t ask me how these records came in ;-) ).

After you have hit that Execute button, you get a couple of warnings. Click Yes on this one:

And after reviewing the parameters, this one too:

Then Terminator executes the script, which is finished in just a couple of seconds (at least on my Dev machine). It states that the table is purged. Click the Disconnect button.

And check if SSMS if the table really is empty.

That’s it!

Don't forget to execute this action on any other BizTalk databases which might have too many records in the MarkLog table.