asked on

LDF file is growing rapidly all of a sudden

All,

If a SQL ldf file is set to grow by 1GB, what does that really mean?

Does it mean that it will allocate 1GB every time it writes to the file?

See attachment.

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

I'd reduce the size so it doesn't take so long to do (unless you have a very fast drive system), or you have that much data being added to that db at one time.

Keep in mind that the entire db waits until the log is extended, and log space must be pre-formatted, which means the entire gb must be written to disk.

Therefore, in general, you don't want the log to grow dynamically at all. Instead, pre-size the log to a good size. Pick an off time and:
1) shrink the log as much as possible
2) reallocate the space in large chunks such that you have no more than say 6-8 increases in log size. For example, if your total log size needs to be 8GB, you might do 4 2-gig extensions, 6 1.5-gig extensions or 8 1-gig extensions.

You don't want too many very small extensions, because that will give you far too many virtual logical files in the log, which will slow down processing as well. Try to keep the total VLFs to no more than 100.

This command will show you how many VLFs the log currently has:

USE <your_db_name>
DBCC LOGINFO

Pavel Celba

You should also look at the Recovery model (in Database Properties - Options) and coordinate them with the backup.
If you are using Full Recovery model without Log backups then the log will grow indefinitely (up to 2 TB in your case).

If you have Simple Recovery model then the space is cleared once the transaction is finished. Remember each data update is first written to the log so e.g. indexes rebuilding can cause the unexpected log growth.