Tungsten (Kofax) Total Agility- The transaction log for database 'PROD_TotalAgility_Documents' is full due to 'LOG_BACKUP' and the holdup lsn is (733:64:1).

Applies to Versions:
7 and above

Summary:
We receive a transaction log error, and emails would not import.

Resolution:
TRUNCATE QUERY USED:
use [PROD_TotalAgility_Documents2]
--BACKUP DATABASE
-- [PROD_TotalAgility_Documents]
--TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\TotalAgility_Documents' WITH NOFORMAT, NOINIT, NAME = N'PROD_TotalAgility_Documents-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
BACKUP LOG [PROD_TotalAgility_Documents2] TO DISK='NUL'
DBCC SHRINKFILE(N'PROD_TotalAgility_Documents2_log',TRUNCATEONLY)
BACKUP LOG [PROD_TotalAgility_Documents2] TO DISK='NUL'
DBCC SHRINKFILE(N'PROD_TotalAgility_Documents2_log',TRUNCATEONLY)

However; here are some database guidelines from Tungsten recommended to prevent this from happening again.

(Details found within the link below as well! Just be aware Tungsten's recommendations cannot use the Simple Recovery Mode.)

https://knowledge.tungstenautomation.com/bundle/TotalAgility_BestPracticesGuide_TotalAgility_bestpractices/page/TotalAgility_bestpracticesguide/c_Database.html

For performance, you should be doing weekly full backups + daily differential backups and Translog backups on 15 Minute Increments as recommended by Tungsten.

You may also want to increase the C:\ since all documents added to Total Agility, those end up into the document 2 database in blob fields. So, you'll want adequate space to handle imported documents.

Sizing

Employ a database server fit for your requirements. The size of the database depends on your overall throughput of data, such as jobs and documents, and solution implementation. For example, solutions that include many long-running cases may require larger databases.

Ensure to employ the TotalAgility retention policies appropriately to manage the database growth.

If auditing is not required, switch it off so that database size does not grow unnecessarily.

Deployment

Install the Reporting database on a separate database server from the core Kofax TotalAgility databases. This reduces the load on the core TotalAgility database server, ensuring optimal performance.
The Documents database should reside on its own physical disk to limit contention of Disk I/O.
Place the database and transaction logs on different drives, preferably separate physical drives.
Distribute the MSSQL files over multiple logical drives. See the sample configuration.

Volume sizes and Input/Output Operations Per Second (IOPS) requirements for each of the items mentioned above vary, depending on the document and user volume on the system.
- SQL Server Installation – E:
- SQL Server database data files – D:
- SQL Server database index files – I:
- SQL Server transaction logs – L:
- SQL Server TempDB data files – T:
- Backups – F:
Ensure all disks containing MSSQL files are formatted with 64K block size.
Ensure all file growth for database files are set values and not the default percentages. Recommended: 256 MB for data, index, and transaction log files.
Configure a service account for all SQL Server services. The MSSQL installer automatically assigns minimum privileges to the services account during installation.
Install only the SQL Server Engine. You do not need additional components, such as Analysis, Reporting and Integration services.
When installing MSSQL, make sure to apply all needed service packs and cumulative updates used by your organization.

Non-production instances of MSSQL should be on the same updates as Production.
Ensure to enable TCP/IP Protocol.
Configure TempDB:
- By default, MSSQL places TempDB on the drive where MSSQL is installed. Move TempDB to its proper location.
- Consider adding multiple data files to TempDB to avoid contention.
- Manually grow TempDB to its final size (sizing varies depending on implementation).
- Do not enable autogrowth for the TempDB files.
Ensure mixed mode authentication is enabled.

Memory

Determine the maximum amount of memory you can assign to SQL Server by subtracting the memory required for the OS and any other instances of SQL Server (and usage on other system if the computer is not wholly dedicated to SQL Server) from the total physical memory. See the Microsoft website for more information on memory usage on SQL Server.

Maintenance

Schedule regular database maintenance plans to manage index fragmentation, statistics, backups and archiving. Typically, the transaction log backups at 15-minute intervals are found to be appropriate, as these occur at a reasonable frequency (ensuring the log does not grow out of control) but not too frequently to impact performance.

You can run an antivirus program on the MSSQL servers but exclude certain files to prevent performance bottlenecks caused by virus scans. See the Microsoft website for a full list of exclusions.

Resiliency

Consider the database resiliency by employing SQL Server clustering, replication, or mirroring. See the High availability section.

MSDTC

Enable MSDTC when transactions span multiple databases. MSDTC is only required in TotalAgility when the Main database tables and the Archive database tables are split across different databases. See the "Install databases" section of the

Kofax TotalAgility Installation Guide

for more information.

TRUNCATE QUERY USED:
use [PROD_TotalAgility_Documents2]
--BACKUP DATABASE
-- [PROD_TotalAgility_Documents]
--TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\TotalAgility_Documents' WITH NOFORMAT, NOINIT, NAME = N'PROD_TotalAgility_Documents-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
BACKUP LOG [PROD_TotalAgility_Documents2] TO DISK='NUL'
DBCC SHRINKFILE(N'PROD_TotalAgility_Documents2_log',TRUNCATEONLY)
BACKUP LOG [PROD_TotalAgility_Documents2] TO DISK='NUL'
DBCC SHRINKFILE(N'PROD_TotalAgility_Documents2_log',TRUNCATEONLY)

Tungsten (Kofax) Total Agility- The transaction log for database 'PROD_TotalAgility_Documents' is full due to 'LOG_BACKUP' and the holdup lsn is (733:64:1).

Error: 'PROD_TotalAgility_Documents' is full due to 'LOG_BACKUP' and the holdup lsn is (733:64:1).