Shrinking a log file

Question

Shrinking a log file

bobba

SSCommitted

Points: 1605
More actions
August 30, 2016 at 8:40 am

#329697

We have a database (SQL Server 2008 Enterprise) while not large - has a lot of disk activity. Recently it was copied over to our Staging environment - where it was noted by our storage administrator that the mdf was 40 GB and the ldf 30 GB. He wanted to shrink the ldf in Staging, which I did, and reduced the size down to 7 GB. He then requested a ldf shrink job on a regular basis in production. My position was not to do it, that we're not gaining anything. With storage allocated already, this will prevent any impact to performance. If we need to reallocate storage for ldf growth, well that could slow things down. More important, like I said, we're not gaining anything.
I can visualize shrinking the files and watching them grow every day. Is this database small enough where we can ignore this concern? This is a busy application, used by multiple departments.
Any thoughts/comments/URLs on this would be appreciated. Thanks!
September 5, 2016 at 12:42 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You are correct. There is no need to run a daily job that shrinks the file, just to see it grow again after few hours. Nothing will be gained and the only thing that will happen is that it will grow again, some operations will have to wait for the log to grow and the number of VLFs will also grow.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sue_H SSC Guru Points: 90860 More actions · Answer 2

You are correct, nothing will be gained and it will only introduce problems. It's just stupid.

Since you want some links, this one has 6 links to other articles about this:

https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

Sue

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 3

The only time to every shrink a log file would be after running an exceptionally large job, other than that your log backups should be happening frequently enough that the log file doesn't continuously grow during normal usage.