Sql-server – Should I shrink the Log File

sql serversql-server-2012transaction-log

I am a developer but have to do DBA stuff now and then.

We had a problem where the Transaction log files of our DBs where increasing to large size of 50GB while the data file (MDF) was only 10-15 GB.

So I tracked the issue to the file size, the Autogrowth was set to 10% for Log File

enter image description here

According to the Start Kit by BrentOzar, they recommend setting the file size to:

256 MB for Data File
128 MB for Log File

Which I have done.

Currently the DBs are in Simple Recovery Mode – Which I intend to change to Full Recovery Mode (and then take regular log backups)

But because the Log Files have grown so large, there is no space on the Hard Disk – I need to make up some space,

The Question is:

Is it okay for me to Shrink some of the log files while they are in Simple Recovery Mode? Because there is alot of free space in some of them

like this one has 99% free space

enter image description here

Another:

enter image description here

Best Answer

Irrespective of Recovery model,shrinking wont help you if there is an active transaction or due to the reasons mentioned here

Transaction log space will be reused in all recovery models irrespective of Size shown in file Explorer,you may still have free space..

Here is what MSDN has to say about Log space reuse..

Log truncation frees space in the log file for reuse by the transaction log. Log truncation is essential to keep the log from filling. Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the Physical transaction log. If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files.

How the log space will be reused is different in different Recovery Models

1.In simple recovery Model,Log Truncation will happen after checkpoints which occur due to many factors as discussed here
2.In Full Recovery Model,TLOG space will be reused only with Backups of Tlog

So even in simple recovery model,Your log space has grown huge which means there is some thing which is preventing logspace reuse..so changing to Full Recovery Model also won't help unless you troubleshoot what is the cause..

You can use below query to see what is Preventing TLOG space reuse..

select log_reuse_wait_desc from sys.databases

Further shrinking Log file is bad for many reasons some of them include 1.file growth operations are expensive
2.Instant file Initialisation wont help for log files

Further Readings:
https://www.red-gate.com/library/sql-server-transaction-log-management

Related Question