00142997 / 00146413 / 00143478
As of SedonaOffice ver 6.2.0.17 The SEFT log is aggregating a lot of data to compensate for several of the changes Forte has made around EFT/ACH Transactions, response codes, timing, reversals, and Z transactions. Accounting for the various conditions, states, and codes of transactions requires regular check ins with Forte to validate data. Development is working to scale the information down more and morein each future release.
Customers that have a lot of transactions on the SO versions above may see their database grow due and need assistance with shrinking this particular table. This guide goes over the steps to do so.
NOTE: Before truncating the SEFT Log Table, ensure the customer has nightly backups in the event they have any Payment Processing issues that require review.
You can access the information via SSMS UI by
Righ-clicking on the production database > Reports > Standard Reports > Disk Usage by Top Tables
Manual SEFT Log Script Check and Truncation below
-- Get table Space sizes
SELECT
t.NAME AS TableName,
SUM(p.rows) AS RowCounts,
SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,
ROUND(SUM(a.total_pages) * 8.0 / 1024 / 1024, 3) AS TotalSpaceGB,
ROUND(SUM(a.used_pages) * 8.0 / 1024 / 1024, 3) AS UsedSpaceGB,
ROUND((SUM(a.total_pages) - SUM(a.used_pages)) * 8.0 / 1024 / 1024, 3) AS FreeSpaceGB,
CASE
WHEN SUM(a.total_pages) > 0
THEN ROUND((SUM(a.used_pages) * 100.0 / SUM(a.total_pages)), 2)
ELSE 0
END AS UsedSpacePercentage
FROM sys.tables t
JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY t.NAME
ORDER BY TotalSpaceGB DESC;
-- Get the Overall Databases Size summary
WITH DatabaseSpace AS (
SELECT
SUM(CAST(size AS BIGINT) * 8.0 / 1024) AS AllocatedSizeMB,
SUM(CAST(size AS BIGINT) * 8.0 / 1024 / 1024) AS AllocatedSizeGB
FROM sys.database_files
),
UsedSpace AS (
SELECT
SUM(a.total_pages) * 8.0 / 1024 AS UsedSizeMB,
SUM(a.total_pages) * 8.0 / 1024 / 1024 AS UsedSizeGB
FROM sys.allocation_units a
)
SELECT
DB_NAME() AS DatabaseName,
ROUND(ds.AllocatedSizeMB, 2) AS AllocatedSizeMB,
ROUND(ds.AllocatedSizeGB, 3) AS AllocatedSizeGB,
ROUND(us.UsedSizeMB, 2) AS UsedSizeMB,
ROUND(us.UsedSizeGB, 3) AS UsedSizeGB,
ROUND(ds.AllocatedSizeMB - us.UsedSizeMB, 2) AS FreeSizeMB,
ROUND(ds.AllocatedSizeGB - us.UsedSizeGB, 3) AS FreeSizeGB,
ROUND((us.UsedSizeMB / ds.AllocatedSizeMB) * 100, 2) AS UsedPercentage
FROM DatabaseSpace ds, UsedSpace us;
--Action
--1. truncate SEFT_Service_Log table
--select * from ss_version --6.2.0.19
truncate table SEFT_Service_Log
If Customer has had issue occur more than once in 90 days they may benefit from the stored procedure Emeka D created
Create PROCEDURE dbo.Sp_TruncateSEFTLog
AS
BEGIN
SET NOCOUNT ON;
-- Clean up any existing temp tables
IF OBJECT_ID('tempdb..#LargestTable') IS NOT NULL DROP TABLE #LargestTable;
IF OBJECT_ID('tempdb..#DataBaseSummaryBefore') IS NOT NULL DROP TABLE #DataBaseSummaryBefore;
IF OBJECT_ID('tempdb..#DataBaseSummaryAfter') IS NOT NULL DROP TABLE #DataBaseSummaryAfter;
-- Get the Largest table info
SELECT TOP (1)
t.NAME AS TableName,
SUM(p.rows) AS RowCounts,
ROUND(SUM(a.total_pages) * 8.0 / 1024 / 1024, 3) AS TotalSpaceGB,
ROUND(SUM(a.used_pages) * 8.0 / 1024 / 1024, 3) AS UsedSpaceGB,
ROUND((SUM(a.total_pages) - SUM(a.used_pages)) * 8.0 / 1024 / 1024, 2) AS FreeSpaceGB,
CASE
WHEN SUM(a.total_pages) > 0 THEN ROUND((SUM(a.used_pages) * 100.0 / SUM(a.total_pages)), 2)
ELSE 0
END AS UsedSpacePercentage
INTO #LargestTable
FROM sys.tables t
JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY t.NAME
ORDER BY TotalSpaceGB DESC;
-- Check if the largest table is SEFT_Service_Log
IF NOT EXISTS (SELECT 1 FROM #LargestTable WHERE TableName = 'SEFT_Service_Log')
BEGIN
SELECT 'Largest table is ' + TableName + '. Aborting!' AS Message FROM #LargestTable;
RETURN;
END
-- Get the Overall Databases Size summary before Truncation
;WITH DatabaseSpace AS (
SELECT SUM(CAST(size AS BIGINT) * 8.0 / 1024) AS AllocatedSizeMB,
SUM(CAST(size AS BIGINT) * 8.0 / 1024 / 1024) AS AllocatedSizeGB
FROM sys.database_files
),
UsedSpace AS (
SELECT SUM(a.total_pages) * 8.0 / 1024 AS UsedSizeMB,
SUM(a.total_pages) * 8.0 / 1024 / 1024 AS UsedSizeGB
FROM sys.allocation_units a
)
SELECT
DB_NAME() AS DatabaseName,
ROUND(ds.AllocatedSizeGB, 3) AS AllocatedSizeGB,
ROUND(us.UsedSizeGB, 3) AS UsedSizeGB,
ROUND(ds.AllocatedSizeGB - us.UsedSizeGB, 3) AS FreeSizeGB,
ROUND((us.UsedSizeMB / ds.AllocatedSizeMB) * 100, 2) AS UsedPercentage
FROM DatabaseSpace ds, UsedSpace us;
-- Truncate the SEFT_Service_Log table
TRUNCATE TABLE SEFT_Service_Log;
-- Introduce a 10-second delay to allow database space to update
WAITFOR DELAY '00:00:10';
-- Get the Overall Databases Size summary after Truncation
WITH DatabaseSpace AS (
SELECT SUM(CAST(size AS BIGINT) * 8.0 / 1024) AS AllocatedSizeMB,
SUM(CAST(size AS BIGINT) * 8.0 / 1024 / 1024) AS AllocatedSizeGB
FROM sys.database_files
),
UsedSpace AS (
SELECT SUM(a.total_pages) * 8.0 / 1024 AS UsedSizeMB,
SUM(a.total_pages) * 8.0 / 1024 / 1024 AS UsedSizeGB
FROM sys.allocation_units a
)
SELECT
DB_NAME() AS DatabaseName,
ROUND(ds.AllocatedSizeGB, 3) AS AllocatedSizeGB,
ROUND(us.UsedSizeGB, 3) AS UsedSizeGB,
ROUND(ds.AllocatedSizeGB - us.UsedSizeGB, 3) AS FreeSizeGB,
ROUND((us.UsedSizeMB / ds.AllocatedSizeMB) * 100, 2) AS UsedPercentage
FROM DatabaseSpace ds, UsedSpace us;
-- Gather Stats report
-- Truncated table information
SELECT
'Truncated Table' AS Information,
TableName,
RowCounts,
RTRIM(CAST(TotalSpaceGB AS FLOAT)) AS TotalSpaceGB,
RTRIM(CAST(UsedSpaceGB AS FLOAT)) AS UsedSpaceGB,
RTRIM(CAST(FreeSpaceGB AS FLOAT)) AS FreeSpaceGB,
RTRIM(CAST(UsedSpacePercentage AS FLOAT)) AS UsedSpacePercentage
FROM #LargestTable;
-- Database Summary before and after truncation
SELECT
DatabaseName,
RTRIM(CAST(AllocatedSizeGB AS FLOAT)) AS AllocatedSizeGB,
RTRIM(CAST(UsedSizeGB AS FLOAT)) AS UsedSizeGB,
RTRIM(CAST(FreeSizeGB AS FLOAT)) AS FreeSpaceGB,
RTRIM(CAST(UsedPercentage AS FLOAT)) AS UsedPercentage,
(SELECT 'SedonaOffice Version: ' + version FROM ss_version) AS [Version],
'Before Truncation' AS Status
UNION ALL
SELECT
DatabaseName,
RTRIM(CAST(AllocatedSizeGB AS FLOAT)) AS AllocatedSizeGB,
RTRIM(CAST(UsedSizeGB AS FLOAT)) AS UsedSizeGB,
RTRIM(CAST(FreeSizeGB AS FLOAT)) AS FreeSpaceGB,
RTRIM(CAST(UsedPercentage AS FLOAT)) AS UsedPercentage,
(SELECT 'SedonaOffice Version: ' + version FROM ss_version) AS [Version],
'After Truncation' AS Status
FROM #DataBaseSummaryAfter;
END
GO
