Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

SBE12.5 won't flush SQL logs

Created: 02 May 2010 • Updated: 03 Nov 2010 | 7 comments
UnderCoverGuy's picture

Good morning.  I am running SBE12.5 on Windows 2008 x32 and all are patched with the latest patches, etc.  I am having problems with backing up SQL 2005 on a Windows 2003 x32 server (patched also).  For some reason, my backups are not truncating the SQL transaction logs.  The SQL recovery model is set to FULL and the SQL server only has one instance on the server.  I am using policies to run the backups and I am backing up to an HP tape library.  One item that I will mention (which I just found out yesterday) is that SQL integration services is not installed on the SQL server.  When i went to create a SQL maintenance plan last night, I was unable to do so because SQL IS isn't installed.  I don't know whether or not that is required for SBE to flush logs.

In previous versions, there used to be an option that you had to check in order to flush the logs but I don't see that available in v12.5.  When I run a FULL backup of SQL, the backup completes successfully but the logs are not flushing.  So I set up another template within the same policy to backup the T-LOG's, which runs (by rule) after the FULL backup completes but doesn't truncate the T-LOG either.  And I had forgotten to exclude the master DB, so it gave me the error of:

"The job failed with the following error: The SQL Server master database does not support log backups.  Only full backups can be performed on the master database."

Even though I got the error, the amount of the backup was the same size that SQL reports the T-LOG as being (the T-LOG is 40GB and the T-LOG backup was 40GB).  So I decided to try again and excluded the MASTER DB from the selection list and ran the FULL backup again, which when complete launched the T-LOG backup, but the T-LOG backup size this time was only about 33MB, and I got an error of:

"A log backup was attempted on a database that is not currently configured to support log backups. To change the configuration use Enterprise Manager to set the recovery mode to FULL in SQL 2000 or turn off the 'trunc on chkpt' option in SQL 7.  A new full backup should be performed if these settings are changed before a log backup is run."

The recovery model is set to FULL in SQL and yet I am receiving this error, so I don't know what I am missing.  Any assistance or direction would be appreciated.

Thank you,
UCG

Comments 7 CommentsJump to latest comment

Dev T's picture

Hello UCG,

Thanks for opening a new thread.....same answer....

Backup Exec will not flush the logs, it will not flush the logs, but it will only mark the logs for deletion.

One unusual element of the Backup Exec SQL Agent is that a full backup does not truncate the log files. If you use SQL's own backup utility, a full backup will backup the database, then truncate the log files. With Backup Exec, you need to run a transaction log backup to truncate the log files. The easiest way to do this is to do a transaction log backup immediately after the full backup.

You can use a script to truncate the logs:

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Its a best practice to have a "Support Contract" with Symantec...
 

UnderCoverGuy's picture

Thanks for the reply.  That's what I am doing, I am using Backup Exxec to run a FULL backup and then a T-LOG backup, but the T-LOG is failing with:

"A log backup was attempted on a database that is not currently configured to support log backups. To change the configuration use Enterprise Manager to set the recovery mode to FULL in SQL 2000 or turn off the 'trunc on chkpt' option in SQL 7.  A new full backup should be performed if these settings are changed before a log backup is run."

Except I do have my recovery model set to FULL.

Am I missing something else?

Thanks,
UCG

Dev T's picture

Hello,

This will be helpful...

http://support.veritas.com/docs/281308

Its a best practice to have a "Support Contract" with Symantec...
 

UnderCoverGuy's picture

Thank you for the replies and your assistance.  In the end, you can chalk this one up to user error (I was looking so hard that I missed the obvious - and I know better than that).  I had set the "user" database to FULL but the system databases were set to SIMPLE (which is why the T-LOG backups were failing - and I kept thinking to myself that I knew I set it to FULL).  And Backup Exec kept telling me that I needed to change the recovery model to FULL (which it was on the "user" database) but it never told me which databases were failing and I didn't even think about it.  That's what I get for working too hard...

Thank you again for the replies!!!
Have a great day!!!
UCG

Dev T's picture

Hello UCG,

Is the backup completed?

Its a best practice to have a "Support Contract" with Symantec...
 

UnderCoverGuy's picture

Yes, the backup completed successfully without any errors.

Thanks

Dev T's picture

please mark it as solution....

Its a best practice to have a "Support Contract" with Symantec...