Large SQL Log backup files

[mmcd]

Hi,

This is curiosity on my end. We have implemented Veeam across our entire VM environment and finally into our production SQL environment. According to our DBAs, transaction log size would never be more than 50GB.

I allocated a drive for Veeam to ship the transaction logs from of 100GB. This is where the weirdness started, during a SQL maintenance window (index rebuilds, update stats etc) there were errors and the log shipping was failing due to space problems on the drive, when I finally was able to increase the size, one of the the transaction log .bak files was 167GB!! According to our DBAs, this is not possible as the transaction log never grew to be more than 60GB at any time.

As I am not overly familiar with SQL, I am wondering what could cause such large SQL log backup files? I am also told that during this maintenance window, recovery model is set to Bulk Logged also.

Thanks for any input!

Martin

[alexander sh]

Hi Martin,

I'll answer in two parts to hopefully give you some perspective.

First of all, a more general remark. Your DBA says that transaction log size would never be more than 50GB. Thing is, transaction log always grows with time (if the database is in a Full Recovery mode, that is), and its size depends on how frequently it is being backed up.
So saying that transaction log never grows over 50GB implies that there is either a hard limit set by a DBA on a transaction log growth (which doesn't seem to be the case here), or that a transaction log is always backed up with a frequency that prevents it from ever growing over 50GB. So one thing to look at is how often you used to back up transaction logs before you started to use Veeam, and what is your log backup interval now (and whether Veeam log backups are actually succesfull).

Moving to more Veeam-specific things, Veeam uses native T-SQL command (BACKUP LOG <dbname> TO DISK = 'target_dir") to back up a transaction log, so I can't really think of a scenario when .bak file produced by Veeam would be different from .bak file produced by native means. Certainly, that is something that would make sense to test, if you decide to open a support ticket.
FWIW, this SO post has some nice info around big TLog backup sizes, and a link to a very useful article on how to analyze/limit TLog growth: http://dba.stackexchange.com/a/93650/29332

[mmcd]

Hi Alexander,

Thank you for the thorough reply.

I forgot to mention that transaction log backups are occurring every 15 minutes on this system.

You are right, there is no hard limit on the transaction log, however, at the time of this backup it was around 40GB in size just (where does the 167GB come from?) . I done some further digging with regard to the the virtual log files within the transaction log, I found that auto-growth is set to 10MB and that there are upward of 3000 VLFs within this log, way more than the recommended 50. Could this be the issue?

Again, I am not overly familiar with SQL.

[alexander sh]

Hi Martin,
thanks for the follow-up.

Regarding 167GB .bak file size, as I said, Veeam uses native SQL backup commands, so there's not much control from other side; I also don't think there's any guarantee from SQL Server that a .bak file must be of the same size or smaller than a corresponding Tlog file, but I haven't really seen any specific info from Microsoft on that.

That said, judging from what I read on the subject, transaction log fragmentation can lead to bigger TLog backup sizes, so it's definitely worth looking into, in my opinion. 10 MB autogrowth seems to be a relic of old-times and usually is frowned upon in the industry. It makes sense to consult with your DBA to see what he thinks about it.

[mmcd]

That's perfect Alexander - thank you for all your help.