1

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.

2

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.

  • I'm still worried how to avoid this thing. If you can help that would be great. – Shashank Tiwari Sep 13 '16 at 17:36
  • You cannot avoid this this is how things are designed. I suggest you increase the disk space where log file resides or preconfigure it. Make sure not to shrink the log files. I am not sure how this is issue – Shanky Sep 13 '16 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.