0

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?

0

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.

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:

  • the secondary server is down
  • OR; the databases are not in a healthy state
  • the redo logs are applying very slowly on the secondary server

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.

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?

No, the log backup nor the shrinking shouldn't cause to the lost of data.

Have you checked if...

  • any scheduled job to purge away some data?
  • application team or the DBA who purge the data?
  • Yes, the log drive was full and we had to extend it. The Databases in secondary node are healthy and AO dashboard shows it all synchronized. We already reached out to Application team regarding any purge operations or jobs and they said nothing is there and they're pretty much pointing only to this log backup. There is nothing else that was done to the system, at least nothing that was tracked – Sriram R May 4 '18 at 17:12
  • Then there should be other causes to the loss of data but not the log backup. A log backup is simply backup the log records produced during the logging process. You may turn on extended events to monitor what is going on with your database system. If it doesn't work out the root cause for you, you may want to open a case with Microsoft to look into it as they will be able to analyze on your system. – Yoshiaki May 9 '18 at 1:18

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.