Door: Cindy Beernink 21-7-2023
Once upon a time there was a client with a request for help. For this customer, we do not do regular management and no active monitoring was set up for that purpose. The customer had noticed that two disks on a SQL Server instance were nearly full. Normally, the disk is then enlarged; after all, it is quite normal for databases to grow. But the customer saw that there might be more to it, as the transaction log files had also grown very large, and asked if we would investigate what was going on. In this blog, Cindy Beernink, database consultant at OptimaData, talks about the importance of monitoring.
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....
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.
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.
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.
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.