patternsqlMinor
Large Transaction Backup
Viewed 0 times
largetransactionbackup
Problem
On my SQL database I run a full backup at 6am and transactional backups every 15 minutes on week days starting at 8am.
Every Monday the first transactional backup is 7 Gb. I want to know why, my database is only 500Mb.
Am I correct in thinking that something is running between 6am and 8am that is causing this massive tran backup? Or could it be something over the weekend that causes the first tran backup to be so large.
Every Monday the first transactional backup is 7 Gb. I want to know why, my database is only 500Mb.
Am I correct in thinking that something is running between 6am and 8am that is causing this massive tran backup? Or could it be something over the weekend that causes the first tran backup to be so large.
Solution
You most probably re-index your database between the log backups. This means your log backup contains a lot of transactions from these maintenance operations.
Please check your maintenance plans.
The size of transaction log can be huge compared to your actual database, imagine this situation. You delete 100k rows and add 100k rows between your backups. Your database will in the end be the same size, but you will have a big transaction log.
Please check your maintenance plans.
The size of transaction log can be huge compared to your actual database, imagine this situation. You delete 100k rows and add 100k rows between your backups. Your database will in the end be the same size, but you will have a big transaction log.
Context
StackExchange Database Administrators Q#101146, answer score: 3
Revisions (0)
No revisions yet.