SQL backup error

[Phil_Tse]

I followed the instructions in the article

https://www-secure.symantec.com/connect/articles/how-backup-sql-logs-and-truncate-them-be-2012

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.

[pkh]

1) What SP level is your BE 2012 on?

2) What version and SP is the SQL database that you are trying to back up?

3) Post the entire error messages that you are getting.  Does the error messages pin-point particular databases?

As an aside,

-Enable "Continue with backup if consistency check fails"

You should disable this option.  If your database does fail the consistency check, then you should correct this before backing it up.

[Phil_Tse]

1) SP 3

2) MQ SQL Server 2008 R2 x64 SP1

3) V-79-40960-37914 - Database **** 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.

I replaced the database name with "****".  Yes, this error does pin-point specific databases.  I get about 8 of these in my exceptions area in the job log.  I have a total of 10 databases selected for this job and I have verified that they are each set with a recovery mode of "Full" in SQL.

[allenjamieson]

Hi There

something that we did on our sql backups (i dont know if pkh will aggree with this method but worked for us)

 switched to just full database backups and setting recovery mode to simple

http://technet.microsoft.com/en-us/library/ms189272.aspx

and got rid of incremental / diffs 

frequent fulls gave us lots of point in times for the databases plus we got rid of the exception errors and also if your running it to a deduplication store, the backups are suprisingly fast

oh and if you do go this route, do some test restores and make sure :)

[pkh]

For one of these database giving the error message, create a separate backup job with a full backup and log backup and run the two backups.  See whether you still get the error message.

I doubt the compatibility level has anything to do with the error mesage.

*EDIT* When did this happen? Did the jobs ever work without the error messages? *END EDIT*

[Phil_Tse]

I created a backup with the same settings as my full backup and log backups and ran them for each database individually.  Out of the 10 databases, only two completed 100% successfully.  The other eight, completed with exceptions.  The same V-79-40960-37914 error code as before.  I checked again to make sure that the eight databases were indeed set to "full" recovery mode in SQL.

Now I'm really stumped because I thought that by testing each database individually, I would find one or two that would be the culprit to my issues, but the exact opposite has happened.  Any further thoughts?

[Phil_Tse]

Thanks for the suggestion.  I'm trying not to change anything on the SQL side of things for now, but I might need to if all else fails.

[pkh]

Did you check with the application/database owner before you change the recovery mode to simple?  Some applications requires full recovery mode.

[pkh]

There is definitely something wrong here.  I would suggest that you log a formal support case with Symantec for them to take a look at this problem.  If you do get a solution, post it here so that others can benefit.

[Phil_Tse]

I took pkh's suggesiton and logged a formal support case with Symantec.  They concluded that I needed to either consult with the database administrator to change the recovery mode of the databases from "full" to "simple".  Or, perform a SQL maintenance job from SQL Management Studio in order to trancate the logs.

Thank you everyone for your help and suggestions.