Research and solution
We started an investigation and it revealed that the full backup and also the transaction log backup had not been running for a week. The backup had gone wrong because the disk had filled up. The customer enlarged the disk for the backup and we managed to create the backups. But….
There’s more to it
The expectation was that this would solve the problem. Enlarging the disk was the solution, right? Yes, that’s part of the answer. Normally when creating transaction log backups, the freed up space in the transaction log files is marked as unused space, this prevents the log file from growing too much and taking up a lot of disk space.
Unless there are still open transactions. The problem here occurs on a high availability cluster. There were still open transactions because the databases were synchronizing with the second replica. This was because the full backup had not run for a week and the transaction log files had become very large.
Preventing worse
Synchronization was restored by removing the second replica from the high availability group, running full backups, and adding the replica to the group again. With this action, we also freed up space on the disk containing the transaction log files.
Fortunately, the customer was not affected by this situation, but things could have turned out differently. When the second replica becomes active, the database is no longer available. If there is no more space for the transaction log files to grow, the database can no longer be accessed.
Monitoring
Could that problem have been avoided? Yes indeed. This situation shows how important monitoring is. Not only to set up monitoring properly with the right signals, but also to react adequately if something goes wrong. Think carefully if there is not still a question behind the question. In this situation, there was no recent full backup in place, and synchronization problems also arose. So you see, one thing leads to another if you can’t identify and react in time.
Want to know more?
Do you have doubts whether your set-up is properly configured or would you like to discuss adequate monitoring? Prevention is better than cure, contact us without obligation, we are happy to help you.