SQL Server transaction log BACKUPS are very large

Question

In SQL Server 2008 R2 I have a database that has full backups nightly and transaction log backups every ten minutes. The database is in full recovery model.

The problem is that the backup files for the transaction logs are hundreds of megabytes in size but should definitely not be. There is nowhere near that activity in the database. Each backup is only for ten minutes. They are so large that after a couple days it'll completely fill up the hard disk.

Any idea what is causing that backups to be so large and how to fix?

Solution 1

Just to be clear we're on the same page, you're saying that each log backup is hundreds of MBs, not that the log files (LDFs) are large. The later is usually explained by log being pinned by replication, mirroring, a forgotten active user transaction or anything else as listed in sys.databases.log_reuse_wait_desc. However, I understand that is not the case. If the log backups taken every 10 minutes each have hundreds of MBs then it means there must be hundreds of MB worth of activity that occurred in the last 10 minutes. Perhaps there is some activity going on constantly of which you are not aware of?

Of course I assume you do not use the WITH NO_TRUNCATE or WITH COPY_ONLY syntax for the log backup task.

The following 3 queries should give you an idea about what is the log filled up with:

use [<myproblemdb>];
go

select count(*) as CountLogRecords, 
    sum([Log Record Length]) [Total Log Length]
from fn_dblog(null, null);

select count(*) as CountTransactionRecords, 
    sum([Log Record Length]) [Transaction Log Length],
    [Transaction ID]
from fn_dblog(null, null)
group by [Transaction ID]
order by 2 desc;

select count(*) as CountLogOperations,
    sum ([Log Record Length]) as [Operation Log Length],
    [Operation]
from fn_dblog(null, null)
group by [Operation]    
order by 2 desc;

Solution 2

How much data is within the transaction log file before the backup is taken? You can see this with DBCC LOGINFO or by looking at the disk space report within SQL Server Management Studio. How big are the log backups?

Solution 3

You may have too many VLFs. Run DBCC LOGINFO and see how many rows are returned. If there are a large number consider shrinking the database's transaction log and growing it in one step to an appropriate size.

Comments

[Robert Kaucher]

How large are the actual ldf files and when you run the backup are you trucating?

[Dustin Martin]

It is 1.3GB which, after looking at it, is probably way too large as well. This database just doesn't get much activity. It is as if the backups are not truncating the log after they are made.

[Dustin Martin]

The log backups are around 300mb each. I'll have to get back to you about the other thing.

[Ryan Ferretti]

Also look to see how much data is in the logs just after the log backup runs. If you can post that back as well, that'll help.