K2

How to shrink the K2 Server database log file

It every so often happens that your K2 server’s performance slowly but surely grinds to a halt, and this is often due to the server running out of disk space. The first thing to check in such a scenario is the size of the K2 database log file. It often happens that this file has grown to an unmanageable size.

This file is usually (on a default installation) located at the following location:

D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\K2_log_1.ldf

There is a lot of steps which can be taken to prevent this uncontrolled growth, those are however beyond the scope of this blog post. For long term SQL logfile maintenance topics please refer to the following article (which strongly advices against doing what I am recommending below):

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/27/oh-the-horror-please-stop-telling-people-they-should-shrink-their-log-files.aspx

The hack below should be seen as exactly that: a hack. It is advisable to only attempt this when you are in dire need of additional space on specifically a DEV or QA server.

Seeing that we are operating in a DEV/QA environment and do not really care about point in time recovery we can first set the databases recovery mode to simple by executing the below command.

ALTER DATABASE [K2] SET RECOVERY SIMPLE;

Putting the database in SIMPLE recovery mode ensures that SQL Server re-uses portions of the log file instead of growing to keep a record of all transactions. Next we need to instruct SQL Server to reclaim the disk space we just potentially freed up by executing the shrink file command. (We use CHECKPOINT events to help control the log and make sure it doesn’t need to grow unless a lot of activity has been generated between CHECKPOINTS)

USE K2;
GO
 CHECKPOINT;
 GO
 CHECKPOINT; -- run twice to ensure file wrap-around
 GO
DBCC SHRINKFILE(K2_log_1.ldf , 200); -- unit is set in MBs
GO

After these commands your log file should have shrunk to 200MB

You may also like

Leave a comment

PODCAST  |  Why you need to establish a K2 Center of Excellence today

Interview with Arno van Rooyen, CEO, Velocity