Transaction log files not shrinking after tlog backups - sql 2005

Question

Transaction log files not shrinking after tlog backups - sql 2005

MissN

SSC Enthusiast

Points: 147
More actions
March 28, 2008 at 5:30 am

#120608

Hi all
I have found that on at least a couple of our sql 2005 servers the transaction logs have been expanding consistently and are not getting trimmed down, even after tlog backups are performed. I have read an MS article and it seems that the log files are truncated when tlog backups are taken, but the physical files are not actually shrunk. I think this must be only in 2005 as this doesn't seem to be an issue with sql 2000.
Here's the article i read:
http://technet.microsoft.com/en-us/library/ms189085.aspx
So if the logs are not automatically shrunk then I assume i will need to set up a job on all my sql 2005 servers to regularly shrink the log files. Bit of a maintenance headache i'd say! Hopefully Microsoft will address this in the next service pack.. anyone else have any info on this?
Also - a useful bit of info - if you've ever used backup log with truncate_only before shrinking your logs - beware! It invalidates your tlog backup sequence, despite the fact that it doesn't actually take a physical backup. I wasn't aware of this until recently - thankfully i didn't have to learn the hard way!
Thx

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

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

It actually costs to increase you transaction log file size frequently and the physical file can become fragmented, etc. Why are you trying to shrink it? I.e. if it will grow back anyway, then you should not do it. If your log grows unacceptably quickly, then you may want to look into why the transaction log is growing at the rate it is growing, and you may fix that (e.g. switch to bulk recovery mode for minimally logged operations). If the amount of transactions you are having require a larger log file, but this file size is stable (i.e. not growing more) and is relatively utilized, then you should leave it at that size.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

I would advise you not to shrink your logs regularly. Let them grow to whatever size they need to be and then leave them. You can reduce the size to which they are likely to grow by increasing the frequency of your transaction log backups. Shrinking them only for them to grow again consumes system resources and is likely to lead to physical fragmentation your disk.

John

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

MissN (3/28/2008)
Also - a useful bit of info - if you've ever used backup log with truncate_only before shrinking your logs - beware! It invalidates your tlog backup sequence, despite the fact that it doesn't actually take a physical backup.

Probably more correct to say it invalidates the transaction log backup chain because it doesn't actually take a physical backup.

Truncate discards inactive tran log entries (while a normal log backup first backs those up then discards them). Since log records are missing, recovery is not possible past that point, until a full/diff backup is taken.

SQL 2000 also did not actually shrink the transaction log files.

If you're having a problem with log backups not clearing out inactive log records, which may cause the transaction log file to grow beyond expected, take a look at the sys.databases view. There's a column in there that shows what is causing log space not to be reused.

Common ones are:

Log backup

Active transaction

Replication

Database mirroring.

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

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 4

Can you please check the log_reuse_wait_desc column in sys.databases table in master database.

select * from sys.databases where name='Yourdbname'

Also if you perform backup log with truncate_only or no_log option it will certainly break the LSN. Also check if there are any open transactions in that database using

DBCC OPENTRAN(Yourdbname)

[font="Verdana"]- Deepak[/font]

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 5

You could try to do the transaction log backups more often so your log will not grow that big. Keep in mind activities like "rebuilding indexes" will make your log file grow.

My blog