Symptoms
Database Maintenance Best Practices
Diagnoses
Beyond the standard SQL Server backup / maintenance plans that we are currently doing, what does K2 recommend for database tier maintenance? Specifically are there certain tables (logs, temporary, etc) that must be purged on some frequency? Any documentation or direction is appreciated.
Resolution
1. One area of consideration is database transaction logs this is true for most applications that use database transactions (whether is is K2, Sharepoint, etc.) and regular transaction database backup/truncation is usually SQL best practice.
https://technet.microsoft.com/en-us/magazine/2008.08.database.aspx
Transaction database backups:
https://technet.microsoft.com/en-us/library/ms179478(v=sql.105).aspx
** As the K2 database is using a "Full" Recovery model, database truncation will occur when performing a transaction database backup
https://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx
** After the transaction database backup, you will also need to perform a Shrinking of the database as "Truncation does not reduce the size of a physical log file. Reducing the physical size of a log file requires shrinking the file."
https://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx
2. Another area of consideration is the archiving of K2 reporting data. This is applicable depending on how heavily K2 is used in your environment, how the processes were designed, and the usage of K2 reporting data.
http://help.k2.com/onlinehelp/k2blackpearl/UserGuide/4.6.11/webframe.html_Reference-WS_MCon-Workflow-Archiving.html
http://help.k2.com/kb000322
**K2 Archiving will move the reporting data to the archive database created this does not reduce the database's size. To reduce the size of the database you will also need to perform the SQL operation to Shrink the database as per:
https://msdn.microsoft.com/en-us/library/ms189035.aspx
