Video Screencast Help

How to backup SQL logs and truncate them in BE 2012

Created: 01 May 2013 • Updated: 19 May 2013 | 3 comments
Language Translations
pkh's picture
+6 6 Votes
Login to vote

If your SQL database is set to Full Recovery Mode, it maintains transaction logs.  If these logs are not truncated from time to time, they will grow and eventually fill up your disk.  BE will warn you that you need to truncate your log.

V-79-40960-37914 - Database database_name is configured to maintain transaction logs.  Transaction log backups are not being performed.  This will result in the log growing to fill all available disk space.  Regular log backups should be scheduled or the database should be changed to the simple recovery mode.

Before you change the recovery mode to Simple, you should check with the database owner because some applications require a Full Recovery Mode database.

To truncate SQL transaction logs on a regular basis, you need to set up a SQL log backup job which will backup the log and truncate it.  This is not so apparent in BE 2012 because BE 2012 requires you to do a full SQL database backup before you can backup SQL logs.

1) Create your SQL backup job.

Make sure you only select the Microsoft SQL Instances.  Do not include files in the backup because you would need to turn off AOF.

2) Edit your SQL backup job

BE 2012 - Edit SQL backup job.png

3) Turn off AOF

You would need to turn off AOF by unchecking Use snapshot technologs.  Otherwise, you may encounter problems when you try to re-direct your SQL database restore later.

BE 2012 - Turn off AOF.png

3) Check that the incremental job is backing up the transaction log

BE 2012 - Log backup job.png

If you do not want to do differential backups of your SQL databases, then this is all for the SQL part.  All you need to specify the schedule for the jobs, the media to use, etc.

You should schedule the log backup to be done AFTER the full database backup.  If you run the log backup before the full database backup and the database backup fails, then you might end up with nothing to recover your database.

4) Set up your differential SQL database backup

This step is optional.  Add another incremental job.

BE 2012 - add job.png

I know it is strange to add an incremental job to do a differential SQL backup, but you can only do differential backup on SQL databases, not incremental backup.

5) Change the method of the additional incremental job

BE 2012 - Differential SQL backup job.png

If you have differential database backups, you can either do your log backups after the full backup or after the full and differential backups.

 

Notes

a) You can also truncate the SQL transaction logs on a one-off basis.  To do this, set up a one-time backup job, select only the SQL databases and then turn off AOF.  You then choose log for the backup option as in the screenshot below

BE 2012 - One-time SQL backup.png

b) Note that truncating the transaction logs does not recover the space occupied by the logs.  You would need to compact the logs to recover the space.  See these documents

http://www.symantec.com/docs/TECH33654

http://www.symantec.com/docs/TECH82950

c) If your database is set to Simple Recovery Mode and you attempt to do log backups on them, you will get this warning message

V-79-57344-33960 - A log backup was attempted on database discover that is not configured to support log backups. To change the configuration, use the SQL administration tools to set the recovery mode to Full.  A new full backup should be performed if this setting is changed before a log backup is run.

Note that some databases, like the Master database, cannot be set to Full Recovery Mode.

If you have a mixture of databases, i.e. some with Full Recovery Mode and some with Simple Recovery Mode, then you need to set up two backup jobs.

Job1 - with log backups, for databases with Full Recovery Mode

Job2 - without log backups, for databases with Simple Recovery Mode.  In Step 2 above, you would delete the incremental job.

Comments 3 CommentsJump to latest comment

EvgeniyL's picture

Very good post. Do the same article for SBE2010R3

0
Login to vote
pkh's picture

For earlier versions of BE, it is rather simple.  All you need to do is to set up a SQL backup job and select Log in the backup option.  It can be stand-alone and either scheduled or one-off.

The reason for this article is that for BE 2012, to schedule the log backup, it has to be part of a backup job.  It cannot be a stand-alone job.

0
Login to vote
Phil Tse's picture

I followed your instructions but I am still receiving a "Job Completed with Exceptions" status.  In my job log, I am still getting the V-79-40960-37914 code in the exceptions area.  I double-checked my database selection for this job to make sure that every database selected is indeed set with the recovery mode of "full".  Like the instructions, I have one full and two incremental backups scheduled within this job.  AOF is disabled.  The first incremental job is set with the "Incremental method for Microsoft SQL" setting to "log" and the second incremental job is set to "differential".  I also staggered the start times of each job.  At this point, I'm not sure what I am missing.

I did notice in my SQL Server Management Studio, that some of the databases are set with a compatibility level of "SQL Server 2005 (90)" and others have "SQL Server 2008 (100)".  Do I need to account for these settings anywhere within Backup Exec?  For my current backup job, under the Microsoft SQL section, I have the current settngs configured for all three jobs:

-Consistency check before backup: Physical check only
-Enable "Continue with backup if consistency check fails"
-Consistency check after backup: None
-Disable "Use checksum on backups"
-Disable "Create on-disk space of SQL backups to be placed on the SQL server where the database is located"
-SQL Server 2008 Enterprise Edition software compress: None

Any help or suggestions would be greatly appreciated.  Thank you.