I've recently taken over a SQL Server database that didn't have any transaction log backup routine running for at least 2 years. The first log backup was very large (3.8 gb on a 1.14 gb database) as you would predict, however subsequent log backups are still larger than I would have expected (somewhere in the region of 3 mb).
I also restored the database to a QA and ran several sequential log backups without there being any activity on the database and the log file backups were still ~3 mb.
What would account for the size and consistency of the transaction log backups with nominal activity on the database itself?
EDIT:
Here is the script we're using to run the TLOG backups, this is being executed every 10 minutes:
BACKUP LOG [mydatabase]
TO DISK = @fullpath
WITH RETAINDAYS = 2
, NOFORMAT
, NOINIT
, NAME = @bckName
, SKIP
, REWIND
, NOUNLOAD
, STATS = 10;
Best Answer
Having spent some time looking at this in more detail it would appear that the unusually large transaction log backups are due to excessive internal fragmentation of the transaction log itself. This is due to unrestricted autogrowth of the transaction log over a long period of time.
The following excerpt from a SQLSkills.com article more precisely defines the problem
On this particular database it turns out we had 3011 VLFs, some degree over the recommended limit of 50 :)
We are now working through the steps in the above guide to reduce the number of internal VLFs. The QA database has been shrunk and the start size set to 2.5 gb, we now have 23 internal VLFs. So the resulting TLOG backup is in the region of 128 kb.