Too many .ldf

Question

Post reply

Too many .ldf

ramyours2003

SSChampion

Points: 12266
More actions
January 25, 2022 at 11:34 am

#3976920

what is the impact of too many .ldf''s in a database?
January 25, 2022 at 3:02 pm

This was removed by the editor as SPAM

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

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

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 1

too many ? There should only be a single one !

ref: https://blog.sqlauthority.com/2020/04/11/sql-server-multiple-log-files-to-your-databases-not-needed/

( I only temporarily used a second ldf file to be able to extend to a new disk and avoid down time )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ScottPletcher SSC Guru Points: 100946 More actions · Answer 2

SQL will separately use each one in order as the previous one gets full. That is, only one log file per db is ever used at one time. Unlike data records, SQL does not spread log records across multiple files.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

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

ramyours2003 wrote:

what is the impact of too many .ldf''s in a database?

To be honest, there's no impact other than maybe on your reputation if someone else finds out... that's also why, except for one instance, you only need one. That one instance is for a "safety overflow" and it's a very rare thing to need one.

--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)

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 4

I have seen some databases with two transaction log files. One on one drive with a max size and another one on a different drive. The reason for this as I was told is free space on the first log/drive is low and when a huge transaction happens it fills up the drive so they created a second log file on a different drive and shrunk the first log file and capped how big it can get.

Steve Jones - SSC Editor SSC Guru Points: 734444 More actions · Answer 5

That explanation is why you have two log files. If you need xx amount, and no drives can support that, you can use two ldf files across two drives.

Say I need 1TB of log between backups. If I have a 500GB mounted drive and a 700GB mounted drive, I can put a log file (ldf) on each and cover the 1TB I need. If I change to a more frequent backup strategy, and only need 450GB at the max size, I can get by with one.