LDF is not automatically shrinked with simple recovery mode

Question

LDF is not automatically shrinked with simple recovery mode

mnemonicator

SSC Enthusiast

Points: 177
More actions
May 23, 2022 at 3:50 pm

#4037702

Hi there, we have SQL Server 2014 (Build - 12.0.6259.0) and my database have simple recovery mode. Problem is that transaction log file is not shrinked automatically, current size on the disk is 250Gb, at the same time i can manually shrink it to 1MB. The same behaviour i can see for other databases with simple recovery mode.
Why?
DBCC LogInfo show me status = 0 for all VLF.

Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

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

ratbak Ten Centuries Points: 1390 More actions · Answer 1

There is an AUTO_SHRINK property, but think hard about using it. Otherwise, the transaction log is only shrunk one tells SQL Server to shrink it by using DBCC SHRINKFILE or DBCC SHRINKDATABASE.

If the log is growing that dramatically, it may indicate

you aren't doing backups often enough; and/or
you should be consider full recovery, with full & log backups, & maybe differential backups; and/or
you have really large transactions that are going to keep growing the log file... which indicates that is really how much space the server needs and you shouldn't keep shrinking it.

frederico_fonseca SSCoach Points: 16196 More actions · Answer 2

with db in simple recovery the following can cause the log to grow.

Replication
CDC
Large size updates (or medium size parallel updates) / same for Deletes
big indexes being rebuilt (or medium size parallel rebuilds (e.g multiple processes))

also have a look at https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver15 - some good info and also tells you how to identify why log isn't cleared down as fast as you may wish it to be

Jeff Moden SSC Guru Points: 1004498 More actions · Answer 3

mnemonicator wrote:

Hi there, we have SQL Server 2014 (Build - 12.0.6259.0) and my database have simple recovery mode. Problem is that transaction log file is not shrinked automatically, current size on the disk is 250Gb, at the same time i can manually shrink it to 1MB. The same behaviour i can see for other databases with simple recovery mode.
Why?
DBCC LogInfo show me status = 0 for all VLF.
Thank you.

Because that's the way it works. Changing to the Simple Recovery Model doesn't do shrinks because shrinks of the log file are generally a bad idea. Using auto-shrink is one of the worst ideas ever because whatever made it grow before is going to cause it to grow again and growing the log file is an arduous task for SQL Server because it has to format the growth with VLFs (Virtual log files).

What you need to do is figure out what's causing it to grow before you do any shrinking. My odds-on "favorite' for what's causing it to grow is people that follow the supposed "Best Practice" Index Maintenance that most of the world has been stuck on for more than 2 decades. It's not actually a "Best Practice", was never meant to be a "Best Practice" and is actually a very worst practice in almost all cases (I say "almost" but I've not yet found a case where it works well).

So, find out what's causing your log file to grow (it might be something other than "Index Maintenance" in the form of bad code) and fix it. Only then consider shrinking your log file.

Whatever you do, don't turn on auto-shrink. Don't even think about it. It just isn't worth it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mnemonicator SSC Enthusiast Points: 177 More actions · Answer 4

Hello Jeff, thank you for you explanation, i absolutely agree with you, but situation i have is that on separate disk for log files i have physically limited space which i can't extend as we have physical server and couple of DBs with really massive transaction fill in disk space and prevent other DBs working properly due to lack of space :/ . Therefore (yes, I did it) I enabled auto-shrink, but it doesn't work :/

homebrew01 SSC Guru Points: 55549 More actions · Answer 5

A one-time manual shrink to a specific size might be better than auto-shrink.

Jeff Moden SSC Guru Points: 1004498 More actions · Answer 6

mnemonicator wrote:

Hello Jeff, thank you for you explanation, i absolutely agree with you, but situation i have is that on separate disk for log files i have physically limited space which i can't extend as we have physical server and couple of DBs with really massive transaction fill in disk space and prevent other DBs working properly due to lack of space :/ . Therefore (yes, I did it) I enabled auto-shrink, but it doesn't work :/

Like I said, even if you think that's necessary, the key here is to figure out what is causing the massive size of the log file to begin with and fix it so that it's no longer a concern. The other databases would benefit from a similar analysis. And, again, I'd be willing to bet it's because of someone's incorrect idea of how Index Maintenance should be done.

The other thing is that you're eventually going to need additional disk space anyway. Buy another disk and install it soon. Whatever it costs (and it's not THAT much), it's cheaper than your time spent farting around with this issue, taking shortcuts that you shouldn't, and the eventual emergency that will occur when you do run out of space.

Ounce of prevention and all that...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)