Hi,
I am currently using Ola Hallengren's backup solution for my backups on a SQL Server 2016 Standard instance. I have a full backup one day a week, differential every other day of the week, then hourly t-log backups. There are two hours each morning--6am and 7am--that the log backups are like 3000 times the size of what the t-log backups normally are. It seems to be a pattern and I'm trying to figure out what's going on in the system at these hours that's causing such large logs to be created. I have way more of a database developer background than administrator, so I'm curious to know what might be the best way to find out what's causing this. I'm not sure if there's a way to retroactively see what was happening at those times or if I have to catch the activity in the moment. There's so much information and so many suggested queries to run to diagnose, I'm not sure which to pay attention to and which are false positives. Does anyone have any ideas on a good way to approach this?
Thank you in advance,
Mike
EDIT: I should also add a few more details:
1) The diff job that runs six out of seven days of the week has the following steps:
STEP_NUMBER STEP_NAME
1 DatabaseBackup - USER_DATABASES - DIFF
2 DatabaseBackup - SYSTEM_DATABASES - FULL
2) The full backup that runs once a week has the following steps:
STEP_NUMBER STEP_NAME
1 sp_delete_backuphistory
2 sp_purge_jobhistory
3 CommandLog Cleanup
4 Output File Cleanup
5 DatabaseIntegrityCheck - USER_DATABASES
6 IndexOptimize - USER_DATABASES
7 DatabaseBackup - USER_DATABASES - FULL
8 DatabaseIntegrityCheck - SYSTEM_DATABASES
9 DatabaseBackup - SYSTEM_DATABASES - FULL
The t-log backups that are quite large happen to be in the couple of hours following these jobs. I thought that perhaps the index optimization might be causing the subsequent two backups to be large, but the index optimization is only happening on one day in the week while the two large t-log backups are happening every day. I can't rule this out completely, but it seems odd that every day I'd need to plan for two gigantic transaction log backups because of db maintenance while the others t-log backups are a much smaller and more manageable...then again, I might be way wrong and just need to expand the disk size...