Hello  @EricLee 

You can shrink the transaction log file if you change the recovery mode from full to simple, using following commands: 

ALTER DATABASE myDatabase SET RECOVERY SIMPLE
DBCC SHRINKDATABASE ( myDatabase , 5 )

then you switch back to full recovery

ALTER DATABASE myDatabase SET RECOVERY FULL

Hi EricLee

How to minimize transaction logs

As a note, if your K2 database is set to Full recovery model, then the transaction log file size will grow rapidly. You may refer to this MSDN article regarding recovery models:
http://msdn.microsoft.com/en-us/library/aa173678(v=SQL.80).aspx

Transaction logs, in general, are auxiliary and contain secondary data concerning the database, K2 or non-K2. All relevant historical information are stored within the K2.ServerLog table so shrinking the transaction log file should not pose any problems. Kindly refer to the following links regarding transaction logs:
- http://support.microsoft.com/en-us/kb/873235/en-us
- https://msdn.microsoft.com/en-us/library/ms189493.aspx
- https://msdn.microsoft.com/en-us/library/ms189563.aspx
- http://www.databasedesign-resource.com/sql-server-transaction-log.html

2. Can we shrink the K2 database? Will this affect active processes?

Kindly consider archiving old data that is no longer deemed as relevant. Please refer to this KB article on performing a K2 archive for more information:
http://help.k2.com/KB000322#

Regards

Elvis

Hi Elvis,

May I know what is the max size limit of the transaction log file that recommended by K2? We would like to schedule task to auto shrink before the it hit the limit.

Regards,

Eric

Hi EricLee,

The installation creates databases with a transaction log size limit of 2TB for K2Server and K2ServerLog

You may also need to consider truncating as well. Please see the following:
Database Truncation:
http://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx
* Full Recovery Model: truncation happens during transaction log database backup
* Truncation does not reduce the size of a physical log file. Reducing the physical size of a log file requires shrinking the file. For information about shrinking the size of the physical log file, see Shrinking the Transaction Log

This value is automatically set by SQL Server if you create the database using the CREATE DATABASE script without specifying any maximum size.

Since this is done by SQL this is not a limitation of our product.
The likelihood of reaching the 2TB Transaction log file is also low in an environment where the databases are backed up regularly.

Kind regards,

Dumisani

​

As a temporary solution, create schedule job backup transaction database log every one hour, and create another schedule to delete trn files every 5 hours or set time.

​