I'm hosting a SQL Server 2012 database server with about 100 databases. I use the standard maintenance plan to backup all user databases daily and do hourly transaction log backups.
One database, which is about 80 MB, backs up fine at 6 AM but the following transaction log is typically 1-2 GB. I've checked and the transaction log is getting properly truncated on the full backup but it immediately fills up after the backup. I've tried switching to simple recovery, shinking the transaction log file, then re-enabling full recovery. That all works fine and the .log file goes down to 4-5 MB. However, within a day it's already up to 2 GB - most of which seems to happen when I perform the full backup. The log has gotten up to 10 GB even though it gets truncated daily.
The database is only accessed through a single web application which doesn't appear to have anything hitting the database during the time where the transaction log balloons out.
I've attempted to query the transaction log itself but can't see anything obvious.
How can backing up an 80 MB database create a 2 GB transaction log? How can I find out what data is actually in the log and determine where it's coming from?