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...
Have you checked the SQL Server Agent jobs to see if any other jobs are running at those times?
Jeff,
I have looked at the other jobs and unfortunately there isn't another one that's running at the same time (or close to the same time). I do have to alter my original statement, though:
"...two large t-log backups are happening every day"
It looks like the two large t-logs are happening after the integrity check and index re-orgs/re-builds on the one day a week that I do those things and not every day. So I'm wondering if it points more to those operations than anything else... If that's the case, it doesn't seem there'd be anything to do. It's not that I'd want to omit those steps or anything, and maybe I just have to live with the fact that I'll have two gigantic t-log backups each week, followed by a ton of normal sized ones...
Mike Scalise, PMP
https://www.michaelscalise.com