We have an install running 11.2.0 running a standard 24-hour full, 6-hour diff, 1-hour log backup schedule. The problem is none of the backups (full, diff, log) are truncating the SQL transaction log. So the log continues to grow to massive size.
Is this a switch or setting somewhere that we’ve missed? Full backups should always be truncating the log when the database recovery model is set to Full.
Hello,
Could you please make sure that “Copy Only” option is disabled at the “Backup options” section?
Also, could you please check your transaction log backups size? Are their size is approximately the same or the next transaction log backup is bigger than the previous?
Copy Only is not checked.
I’ll watch the logs today and compare the size. The database is very active and is about 16gb in size. But the trans log was 120gb in size. There’s no way we had that many transactions in the 1 hour of time between transaction log backups.
Spent the last few days monitoring log file backups. The file size is different each time with much larger sizes during working hours and much smaller size during off-peak hours. This is what I would expect to see.
So I’m not seeing an issue at this point. Yet I have no explanation for why the log file was 120gb last week. Almost sounds as if I’m dealing with an application issue not closing a transaction like it should.
I adjusted the autogrowth settings. I’ll continue to monitor weekly and report back here if I see an issue. Thanks for the response, sorry to waste your time.
Thank you for the feedback. We recommend you to check this issue with the help of the following article https://technet.microsoft.com/en-us/library/ms345414(v=sql.105).aspx