Backup not truncating SQL logs

[David_Henry_2]

Backup not truncating SQL logs

I have a log file backup job running on my SQL server with remote agent every weeknight. The job runs without errors. The problem is that the space used in the file does not decrease after the backup so the log file size continues to grow. I do not wish to shrink the database size, I just want it to truncate.

Thanks for any help.

Details:
MS SQL Server 2000 sp3 on MS Server 2003 SP 1
Symantec Backup Exec 11d Rev. 6235 on both Media Server and RAWS on SQL Server
SQL database is running in Full Recovery Model
SQL Log backup is 'Log - Back up transaction log'

[Jeff_Zankofsky]

David,

BE doesnt clear the log, it will only mark the transactions as inactive. No different than doing a log backup using SQL server. Doing a shrink and telling it to remove unused space only will do what you want.

Here's an article from the Symantec support site that explains it: http://support.veritas.com/docs/253528

[Keith_Langmead]

One thing to note from the link Jeff gave, while it's a very good description of the situation, it doesn't make it clear that going round shrinking the db or log file size isn't generally a good idea unless you know that is is required.

The reason is that when the file fills up SQL has to increase the size of the file, and that process is quite intensive and can have a negative effect on the server. Generally in most situations it's best to leave it alone since after a while the space will be used, then cleared, then used again etc. The exception is where you've done a mass change to the database, like a large import or something, which has increased the filesize larger than it would normally be, in which case it's then worth reducing the size to where it would be normally.

[Jason_Starkey]

I'm actually encountering the same issue. I just moved from Backup Exec 9.0 to 11d and previously everything was working good. My transaction logs would grow to about 10GB, then once my backup ran...be it full or differential, then the logs size would go down to about 5GB. Now, in the latest version, I run a full or backup and then size remains growing...and never comes back down...even if I have the shrink option set on the database.

Thanks,
Jason

[David_Henry_2]

From the first line of the article that Jeff posted: "It is normal for the SQL transaction log files to remain the same size even after they have been truncated. The truncate function only increases the free space in the log file."

From my post: "I do not wish to shrink the database size (I really ment log file), I just want it to truncate."

The problem is that the backup is not _Truncating_ the log file.

Edit added line: (I really ment log file)
David Henry

[Jason_Starkey]

Ditto

[Keith_Langmead]

Ahhhh, good point David, sorry about that... memo to self, don't pay more attention to other people replies than the to the original post! :)

Afraid I don't have an answer for you, but I have seen several other people posting about the same thing on here previously, all relating to version 11 I think, so I wouldn't be suprised if this was yet another v11 issue which has yet to be fixed.

[Jeff_Zankofsky]

Hi Jason,

Full's and Diff's are not supposed to truncate transaction logs. Only a Log backup will do this. Have you tried running Log backups of you db to see if it makes a difference? My only guess is that when you had 9.0 running, someone else was running log backups using query analyzer?

Anyway, I've been testing log backups here on my 2005 server, and 11d seems to be functioning as it should.


Jeff

[David_Henry_2]

Jeff,
Are you using the remote agent or do you have Backup Exec installed on your SQL server?
Did you upgrade from a new version or is it a fresh install?
Thanks, -Dave

[Ken_Putnam]

Yeah, Veritas has had this screwed up since v7

MSSQL full backup will backup database and logs, and truncate the logs.

Veritas full backup will backup the database and logs, but NOT truncate the logs. You need to schedule a LogFile backup(Truncate logs) immediately following (at least v7 and v8 were like this. Haven't been involved with SQL Agent backups since v8.)

We converted to using MSSQL Dump to Disk (which DOES trucate), and then grabbing those files with flat file backups.

[Russ_Perry]

hmmmm, Backup Exec has used Microsoft's SQL APIs since 7.x. Whether through SQL or Backup Exec, logs are NOT truncated on full backups/dumps. Only a log backup will truncate the logs...

Some info from SQL Books Online:

"The size of a transaction log is therefore controlled in one of these ways:

When a log backup sequence is being maintained, schedule BACKUP LOG statements to occur at intervals that will keep the transaction log from growing past the desired size.

When a log backup sequence is not maintained, specify the simple recovery model."

And finally from the original question about the size of the log file. I assume you're looking at the file size in Explorer... From SQL Books Online:

"Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file."

This means the sparce file visible in Explorer will have more usable space in it after a log backup but won't be smaller (unless set to automatically shrink).

Russ

[Jeff_Zankofsky]

David, I am running SQL 2005 and BE 11d on the same server.

One thing you may want to try is running a backup to disk using the Enterprise Manager. See if it truncates the log file properly. Maybe it will clear up issues that Backup Exec is having.

[David_Henry_2]

Enterprise Manager truncates the log file just fine. It just seems to be the Backup Exec. I'm wondering if it is the remote agent or the upgrade.
-Dave

[David_Henry_2]

Latest update: Well I found our vendor installed a script on the same day that I upgraded to BE 11d that they didn't bother to tell us about. The script runs during our backup every night and 'defragments' the indexes. I have disabled it and I am continuing testing.

Thanks for all the help so far. -Dave