We have two DB servers which are part of Always On. Recently we noticed that log backups haven't been running on them for sometime and their .ldf files were growing. We took a log backup and tried to shrink the log file. Not sure if the shrink actually ran, the wizard closed in 2-3 secs and the .ldf file size didn't reduce.
Later that day it has been found that data as far as two years old have been lost in specific tables in that DB. Impacted tables are less than 10, There are around 500 tables in the DB and more than 100 of them involve daily transactions.
Please clarify if the log backup and shrink has any part to play in this?
If yes, how could this have been avoided and what else was supposed to be done to keep the .ldf in check?
Best Answer
Did you check that if both of the AlwaysOn servers are in a healthy state? It seems that the log backup is not happening because the principal logs were still holding up, which could be due to the following reasons:
Look into the SQL error logs as well as sometimes log backup does not work because the disk is full, and it couldn't write to it.
If log usage is still high, you may remove the affected databases from the AlwaysOn availability group first. Wait till the log usage drop, and then add back the affected databases into the availability group. This shouldn't cause to any impact.
No, the log backup nor the shrinking shouldn't cause to the lost of data.
Have you checked if...