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 FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAK2_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

Need further technical advice or support?