I am having two SQL Server 2016 machines with mirroring set up. This is supposed to be hot-standby scheme.
HDD size is 1TB, and database size is around 600GB (just one DB). This is 90 days worth of archived data, everything older than 90 days is getting deleted every night (automatically through an external application which is using/filling the database in the first place). So 600GB is the peak DB size, it will not go beyond as it is being cleaned up regularly.
The problem is with the transaction log if one server fails, or if mirroring gets suspended for any other reason. If I understood the principle correctly, healthy server will retain the transaction logs as long as it doesn't get information from partner that everything is OK. So if mirroring fails, the HDD will get filled within several hours.
Is there any suitable technique to prevent this? I have backups of logs every 15 minutes and everything works fine, but if mirroring gets suspended, backups are not worth much, as the log will keep growing in spite of the backups. And the situation on site is a bit specific, there are no engineers, only operators who are accessing this data once or twice per day, so it's impossible to react straight away. It can take more than 24 hours to have someone attend the problem.
Only thing I could think of is some sort of trigger that would remove the mirroring completely once it was suspended for some time (or maybe if it is suspended and HDD space is too low). This will prevent the healthy server from crashing completely, but someone will again have to come to site and set the mirroring up from scratch. And due to bad design from the start, DB size is bigger than half of the HDD size, so I can't even create local backup/restore, I would have to do everything via the 100Mbps NAS that belongs to the client. And this would take more time than it would take the transaction log to fill the drive again.
Now I am thinking to set up a script that would execute every half an hour for example, and remove mirroring if log size is higher than let's say 200GB. Any prettier way to do this? Also, what will happen with mirroring if log hits the maximum size? All transactions are small and can be rolled back quickly, so I am also thinking to set the maximum log size a bit higher than previous threshold (250GB maybe) as an additional precaution. Any downside to this?
Edit 1: I want to put this in initial question as well, maybe my question was not clear enough, but I do not have issues with mirroring getting suspended, it is working perfectly (for now), I am just preparing for worst case. Main goal is full redundancy, and without any precautions, application will become unusable because of full hard drive if just one computers fails, it will happen within hours.