Enormous VeeamBackup_log.LDF

[felyjos]

Hello,

I just discovered the VeeamBackup_log.LDF is over 67 GB and causing space issues now on the drive.
How to discrease this size? Apparently the shrink in SQL Management Studio does not do anything.
I ran a backup on VeeamBackup_log.LDF and still no change for the size.
I ran a backup FULL of VeeamBackup Database and nothing chnage.

Please advise,

Thanks,
Dom

[felyjos]

The recovery mode is "FULL" could I change it to "Simple" shrink the log and back to "Full" what is expeted by Veeam to avoid this huge growth of the log file?

[tsightler]

If you're not performing regular SQL backups that purge logs (Veeam can backup itself and do this), you could choose to just leave it in simple mode. Actually you could probably just leave it in simple mode, not much reason for full recovery mode of the Veeam DB.

[felyjos]

We have daily backups but apparently the file is growing faster than expected (1 day) or it(the backup) does not do what is expected...
It is a Maintenance Plan.

[tsightler]

Does the maintenance plan include a backup? I've never seen any scenario where the logs grow that much in a single day. You can always open a support case if you feel that is happening as that wouldn't be normal.

[felyjos]

Thanks for your follow-up:
Yes there is a backup inside the Maintenance Plan:
EXECUTE master.dbo.xp_create_subdir N'D:\SQL VeeamDB Backups\VeeamBackup'
GO
BACKUP DATABASE [VeeamBackup] TO DISK = N'D:\SQL VeeamDB Backups\VeeamBackup\VeeamBackup_backup_2013_02_26_151131_9536896.bak' WITH NOFORMAT, NOINIT, NAME = N'VeeamBackup_backup_2013_02_26_151131_9536896', SKIP, REWIND, NOUNLOAD, STATS = 10

Let me know if something is missing.

Thanks,
Dom

[tsightler]

Full backups do not truncate the transaction log. You need to either backup the transaction logs, manually truncate the log (with BACKUP LOG with TRUNCATE_ONLY or on 2008 and newer run a fake LOG back to NULL) or switch to simple recovery mode (and potentially back to Full recovery, but only if you configure some transaction log backups). Since you're not making transaction log backups you effectively can only do the equivalent of "simple" recovery anyway so that's a perfectly valid option. Here's some light reading on the subject.

http://sqlinthewild.co.za/index.php/200 ... tion-logs/
http://www.brentozar.com/archive/2009/0 ... rver-2008/
http://blog.migrationking.com/2012/03/t ... -2008.html

[felyjos]

Excellent info
Thanks a lot
Dom