creating new ldf file

Question

creating new ldf file

m.rajesh.uk

Hall of Fame

Points: 3653
More actions
July 8, 2013 at 4:41 am

#295394

there is one ldf file for our database. if we create one more ldf for the same database. then both of these ldfs are shared for transactions or after filling one ldf next one will comes in place.

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

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 1

m.rajesh.uk (7/8/2013)
there is one ldf file for our database. if we create one more ldf for the same database. then both of these ldfs are shared for transactions or after filling one ldf next one will comes in place.

The transaction log is written to sequentially. This means that if you add another log file, it will only be used once the first one has filled up completely.

Note that there is absolutely no performance gain from having additional transaction log files.

m.rajesh.uk Hall of Fame Points: 3653 More actions · Answer 2

I created second ldf file with initial size as 2 mb and filegrowth option is set as unrestricted to both ldf files. when i saw after some time the first ldf is 113 mb second ldf is 6 mb. so why it is using secondary ldf.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It will use the first then the second then the first again then the second again, etc. The log files will only grow if both are full.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

paul.knibbs SSCoach Points: 15320 More actions · Answer 4

GilaMonster (7/8/2013)
It will use the first then the second then the first again then the second again, etc. The log files will only grow if both are full.

Just curious--will SQL server grow both log files according to their auto-growth settings at that point, or just the one it was using at the point it ended up with them both full?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

paul.knibbs (7/9/2013)
GilaMonster (7/8/2013)
It will use the first then the second then the first again then the second again, etc. The log files will only grow if both are full.
Just curious--will SQL server grow both log files according to their auto-growth settings at that point, or just the one it was using at the point it ended up with them both full?

Don't recall. Try it and let us know?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass