Should I shrink the Log File

Question

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

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

Another:

Answer 1

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

Answer 2

There is no harm in shrinking your transaction log file from time to time to free up space, with a firm understanding that it might grow again over time.

Be aware too that every time it grows, it writes zeros to the file, so it will be dependent on the speed of your storage and could affect the performance of SQL Server while it's growing. That's why we recommend only shrinking or growing the file under specific conditions.

Switching to the Simple Recovery Model is a decision that should be made based on business requirements, and not taken lightly. Having said that, Simple Recovery should mitigate blowing out the log file as you've seen, as would setting the growth to a fixed value, which you've done.

Don't try shrinking it too much (or too often), because as I said, it can and will grow again, especially with long-running transactions moving lots of data (e.g. deleting from a large table, regular index maintenance, etc.).

A good rule of thumb: if you shrink it, and it grows to a certain size, that's the size it should be, and that's where you leave it.

For future reference, frequent transaction log backups will have the same effect as switching to Simple Recovery, in that it clears out the inactive portion of the log file, so that it can be reused by newer transactions.

Answer 3

I'd recommend the same approach as Randolph, changing the recovery model is not going to fix your problem neither is constantly shrinking the log file, you need to really discover what is causing the log file to grow and sort the root cause.