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 24x7 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.