x

Why is my LDF Log File so big / getting bigger / bigger than my MDF

I have a 170MB database with a 56GB transaction log. I thought that doing a full backup of the database would also clear the transaction log, as a full backup makes the old transaction records obsolete. I did a full database backup, and then I shrank the log file, but it still is 55GB...
How do I shrink it? Also, why wasn't the log shrunk when I did the full database backup?

more ▼

asked Oct 07, 2009 at 04:08 AM in Default

SQLNewbie gravatar image

SQLNewbie
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

5 answers: sort newest

Doing a transaction log backup doesn't shrink the file, it only frees up space, which means doing any form of backup whether full or log will free up that space to accomodate new transactions.

After you've backed up, you can shrink the file. You can do this depending on the MSSQL version you are using, check Books Online.

Remember that shrinking may not always be your best solution to manage the log file size. It may be that your application accessing the database needs that certain amount of log file to complete it's required operations. If it doesn' Kristen's reply would guide you.

more ▼

answered Oct 07, 2009 at 05:39 AM

Jen gravatar image

Jen
37

(comments are locked)
10|1200 characters needed characters left

Just my two cents. I encountered the same issue. It has to do with the recovery model of your backups. If you set the recovery to simple from full, then shrink the log file then you can set it back to full recovery if you need to. That will shrink your .ldf.

more ▼

answered Oct 22, 2012 at 04:25 PM

Rusty gravatar image

Rusty
0

(comments are locked)
10|1200 characters needed characters left

See http://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html

Why wasn't the log shrunk when I did the full database backup? You can think of the tr log like this

The tr log is like a circular file (It deals with virtual files rather than entries but that's not really important). It holds a pointer to the oldest row that is in use (uncommitted or not checkpointed) and the oldest row that can be reused i.e. is no longer needed, is released. Uncommitted rows can't be released so any long running transactions will cause the tr log to expand.

An entry is released when either a tr log backup is performed or the database uses the simple recovery model (and a checkpoint is performed) (and the row is copied to the distributer if involved in replication). A full backup does not affect the tr log with respect to releasing rows. The idea is that tr log backups and full backups are independent. You can restore the full sequence of tr log backups after any full backup - so if you take two full backups then a tr log backup you can use that tr log backup to restore from either of the full backups.

Releasing rows allows the tr log to cycle back and re-use them - this stops the log from growing.
Once the log has grown releasing entries will not cause it to shrink - the file has to be shrunk manually.

more ▼

answered Oct 07, 2009 at 01:52 PM

nigelrivett gravatar image

nigelrivett
92

(comments are locked)
10|1200 characters needed characters left

You need to back up your log file, freeing up space, then shrink it back down to a reasonable starting point. Once you get into a routine, the log file will "settle down" and basically stay about one size for each back up period. If this size is still too big for your liking, just do backups more often.

more ▼

answered Oct 07, 2009 at 01:17 PM

kctechnopro gravatar image

kctechnopro
22 1 1 1

(comments are locked)
10|1200 characters needed characters left

Do you make transaction backups?

Yes -> Do not shrink the log unless you have done some exceptional operation (such as a massive delete) as the "cost" of SQL Server re-growing the Log file is significant, and will lead to fragmentation and thus worse performance.

No and I don't want to -> Change the Recovery Model to "Simple" - Enterprise Manager : Right click database : Properties @ [Options]

Don't know -> Transaction log backups allow you to recover to a "point in time" - you restore the last Full Backup (and possibly a subsequently Differential backup) and then every log backup, in sequence, until the "point in time" that you want to roll-forward to.

The more frequently Transaction log backups are scheduled the smaller each backup will be, and the less space that will be required for the LDF file - the Transaction Log Backup is what "truncates" the LDF log file (but it does NOT shrink it)

It is "normal" for LDF filesize to be about 120% of the MDF filesize - less than that there is no point shrinking the Log File as it will most likely grow back up to 120%

If you do Index Rebuilds (either as part of your housekeeping, or because you "ticked the box in Maintenance Wizard" ) then when these run they will cause significant logging activity (and potentially large LDF file size). I have seen many places that run Transaction Log Backups just from 8am to 5pm, and then run an Index Rebuild overnight and wonder why their LDF files are so big!

If you are running transaction log backups I can't think of a reason not to run them frequently - by that I mean every 10~15 minutes. It does mean that there are more files to restore, but in general it does keep a lid on the size of the LDF file - particularly if your Index Rebuild takes an hour or two!

To shrink the log use:

backup log MyDatabaseName with truncate_only

more ▼

answered Oct 07, 2009 at 04:10 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x3

asked: Oct 07, 2009 at 04:08 AM

Seen: 23617 times

Last Updated: Oct 22, 2012 at 04:25 PM