That's me again.
Finally we had the approval to implement the above recommendations but unfortunately we have now another issue this time. Let me explain what we have done and the current scenario.
After several recommendations and to not braking the log chain, before starting index rebuild tasks the DB is set to bulk-logged recovery mode. This task ran for more than 24hs causing this task to overlap with the full backup one (which runs outside the maintenance plan). By the time the backup is scheduled a huge backup log was generated (almost 56.00 GB) causing the drive unit got full. For extra clarifications we use an external backup tool to perform this backup log. At that point, the following actions have been performed:
A- Maintenance Plan job was stopped
B- Cleared up the drive unit where the backup logs files (*.CFT) are stored. 150 GB free space available.
C- Application running on that server stopped.
D- Set the DB recovery mode back to Full.
The issue is that the backup log file is growing continuously with not limit apparently. He have stopped backing log task to avoid the unit drive to get full again and by that time the CFT file was 112 GB.
No active jibs running on that sql server neither running activities are being shown in the activity monitor.
Do you have any idea why these huge files are being generated? How can I determine what could be causing this behavior.
Please let me know if some extra detail is required to clarify the situation.
Thanks in advance for all your support.