Maintenance Guide

Maintenance should be done periodically against the TctepDb and TConnectEDI databases. The following scripts should be executed as needed.

Rebuilding Indexes

The below script will rebuild all indexes on TctepDb and some of the TConnectEDI databases.  This script should be executed during a maintenance period where the Tctep services are stopped.

It is important to note that these scripts are provided as an easy to understand starting point for a broader SQL Server maintenance plan.  These by themselves do not represent a complete solution.

USE TctepDb 
go 
exec sp_MSforeachtable 'BEGIN TRY ALTER INDEX ALL ON ? REBUILD END TRY BEGIN CATCH END CATCH'; 
GO
exec sp_MSforeachtable 'BEGIN TRY ALTER INDEX ALL ON ? REBUILD END TRY BEGIN CATCH END CATCH'; 
USE TConnectEDI834 
go 
exec sp_MSforeachtable 'BEGIN TRY ALTER INDEX ALL ON ? REBUILD END TRY BEGIN CATCH END CATCH'; 
USE TConnectEDI835 
go 
exec sp_MSforeachtable 'BEGIN TRY ALTER INDEX ALL ON ? REBUILD END TRY BEGIN CATCH END CATCH'; 
USE TConnectEDI837D 
go 
exec sp_MSforeachtable 'BEGIN TRY ALTER INDEX ALL ON ? REBUILD END TRY BEGIN CATCH END CATCH'; 
USE TConnectEDI837I 
go 
exec sp_MSforeachtable 'BEGIN TRY ALTER INDEX ALL ON ? REBUILD END TRY BEGIN CATCH END CATCH'; 
GO
USE TConnectEDI837P 
go 
exec sp_MSforeachtable 'BEGIN TRY ALTER INDEX ALL ON ? REBUILD END TRY BEGIN CATCH END CATCH'; 
GO

Setting SQL File Growth and Size

Each of the SQL Server databases should be configured to avoid automatic file growth whenever possible.  File growth operations are resource intensive and degrade performance while a growth operation is in progress.  To avoid auto growth, the size of each database should be set to account for the expected growth of the database between maintenance periods.  The System Requirements article contains expected database growth metrics crucial to properly setting file sizes. As a fallback measure, auto growth can be set to a large enough increment to avoid excessive growth operations.

The following script provides an easy way to create a script to update the size and growth of all of the T-Connect EDI Gateway databases.

DECLARE @dbInitSize varchar(10) = '1000MB';

SELECT 
'ALTER DATABASE ' + QUOTENAME(s.name) + '
    MODIFY FILE
    (
        NAME = ' + REPLACE(REPLACE(QUOTENAME(s.name),'[',''),']','') + ',
        SIZE = ' + @dbInitSize + ',
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1000MB
    );

    ALTER DATABASE ' + REPLACE(REPLACE(QUOTENAME(s.name),'[',''),']','') + '
    MODIFY FILE
    (
        NAME = ' + REPLACE(REPLACE(QUOTENAME(s.name),'[',''),']','') + '_Log,
        SIZE = ' + @dbInitSize + ',
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 20%
    );'
FROM sys.databases AS s WHERE name like 'TConnect%' or name = 'TctepDb'
GO

Executing the commands in the result window will apply the updated settings to the databases