Transaction Log Not Shrinking After Backup

I have a database that I am backing up the transaction log ever 30 minutes however the log size is always around 850mb regardless if the backup is through the day when work is happening or at 3 am in the morning.

What could be causing this?

I guess there may be some automated data input but is there anything else that would cause the log to be around the same size at every backup.

The recovery mode for the database is FULL.

more ▼

asked Jan 17, 2010 at 04:01 PM in Default

avatar image

39 2 2 4

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

2 answers: sort voted first

Just because the log file is a certain size doesn't mean that it is all used.

Check out DBCC SQLPERF (LOGSPACE) and see whether the amount of log used is changing after your backups.

more ▼

answered Jan 17, 2010 at 04:47 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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

The fact that your log file is always the same size is really healthy. The log file itself won't shrink unless you ask it to. Truncating won't actually shrink the file, just mark it for re-use. If your log file had filled up, it would be needing to grow. Also, your log backups would be large.

How big are your backups? That's often a nice indication about how much data is in your log file at any point in time.

Another way of checking the size of the log file is within Management Studio. Right-click on the database in question, go to Tasks, Shrink File. Don't actually tell it to shrink anything, just have a look to see what the dialog tells you when you pick Log from the "File Type" combo. It'll probably tell you that the file is 99% empty.

more ▼

answered Jan 17, 2010 at 09:58 PM

avatar image

Rob Farley
5.8k 16 22 28

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jan 17, 2010 at 04:01 PM

Seen: 6642 times

Last Updated: Jan 17, 2010 at 04:01 PM

Copyright 2018 Redgate Software. Privacy Policy