Log file increases while differential backup is in progress

Database server: Microsoft SQL Server 2012 (SP1) – 11.0.3128.0

Database Size: 500+ GB

We have a staging history database which stores what so ever operation(Insert, update, delete) is done on live database is getting transferred to our history server via service broker. And that message is processed and stores data in history database.

We recently found a issue that message queue of our destination database is not clearing processed messages. We started cleaning the queue there were around 320+ M records which were stored in this queue.

We are running one script 24×7 which cleans processed messages of the queue.

Now, the problem which started recently is whenever our scheduled History database’s differential backup is progress log file of History database increases to 10+ GB and as soon as it completes everything gets normal.

Is there anything that we are missing or is this is a normal thing?

Transaction backups do run in between differential backups. Usually differential backup takes around 1 hour and in between this one hour time 4 Transaction backups takes place.

Now, the problem which started recently is whenever our scheduled History database’s differential backup is progress log file of History database increases to 10+ GB and as soon as it completes everything gets normal.

So was transaction log backup also running before the differential backup job? Starting from SQL Server 2005 both Differential backup and log backup can be started simultaneously on SQL Server but transaction log truncation would be delayed until the diff backup completes that is why till differential backup is running the log file grows to write transaction after it completes the logs are truncated and space is reused. In your case Log backup would have run while differential backup was running and would not have been able to truncate logs but as soon as it finished the logs were truncated

Full backup and differential backup can delay log truncation because both needs to read some part of log to create consistent backup and if log backup was allowed to truncate the log file this would result inconsistent full and differential backup

This is similar SE thread I answered before.