Problem

In some real life scenarios, concerns may arise about the growing external SQL Server database used by Secure Transport. This KB article will address most common entities that consume space and common ways to decrease this space usage.

Resolution

Introduction

The largest entities that consume space in Secure Transport Database are:

File Groups

ST_DATA

ST_FILETRACKING

ST_SERVERLOG

and, of course, the Transaction Log. We will address all those and some SQL Server utilities you may find useful.

ST_DATA

Normally ST_DATA is not using too much space. It can grow but in the normal ST usage it will reach a point when growth is insignificant. If this is not the case contact Axway Support for assistance.

ST_FILETRACKING

This is where all file transfer metadata is stored - when, what (name and size only) and how is transferred. Those are the tables:

TransferDetails

TransferData

TransferResubmitData

TransferProtocolCommands

SubtransmissionStatus

They are partitioned and have daily partitions. For decreasing the space usage, ST utilizes the TransferLog Maintenance Application (usage documented in the Administrators Guide) - it drops old partitions and so frees space in the File Group.

ST_SERVERLOG

Here we have two main groups of entities:

ServerLog tables:

logging_event

logging_event_property

logging_event_exception

Those three tables are managed by the ServerLog Maintenance Application in ST, which can delete old partitions and so will free up the space used by them.

AuditLog table. This table is used for Audit purposes. Some customers require holding data for months and years and this is the place where all audit records are stored. Normally the customer can configure the AuditLog Maintenance Application to delete the records which are of a certain age.

Transaction Log

SQL Server uses Transaction Log for keeping track of all Transaction data. It has many purposes and is one of SQL Server's critical and very important components. Transaction Log growth, however, should be managed by an appropriate backup strategy. Some customers put the database regularly in simple recovery mode and shrink the transaction log before returning to FULL Recovery mode. It will require some downtime so this way is not very widely adopted. How the growth would be managed depends on the selected backup strategy for Critical Databases.

Some shrink procedures

In some situations, the Shrink Database or Shrink File procedures provided by SQL Server might prove useful. They are not adopted widely as those procedures may lead to very high index fragmentation and result in performance degradation.

DBCC SHRINKDATABASE (STDB, 10);
GO

or

DBCC SHRINKFILE (DataFile1, 7);
GO

More documentation on those tools can be found here:

Shrink a Database

DBCC SHRINKFILE (Transact-SQL)