Printable View
Hi,
log backup of a database has been scheduled at every 30 minutes. Backup size is between 1-2 GB as compared to 20-30 MB earlier. The recovery model of the database is Bulk Logged and Log file is always 95-96 % free between the backups.
How can i find which transaction is responsible for increased backups size when Profiler is not an option?
There is an option in the backup to create a new file or append/add to an existing file. Sounds like you're adding to...
no, a new file is created every 30 minutes. That is why it is a concern that what actually is being done on the database that log backup is in GB.
Does db have many bulk load transactions? If so, log size will be small but log backup maybe big because sql will backup all bulk load related extents during log backup.
yes true log backups is of large size in bulk logged recovery model however, I am not doing any bulk logged transactions and same has been checked with the concerned apps team too and still log backups are of large size and every 30 minutes.
I tried to find out by checking active transactions between the backups however couldn't succeeded.
Bulk logged transactions are not limited to bulk load, reindex is one for instance.
Check if there are any open transactions. Transaction log backup may not be truncating after each backup due to open tran.