cancel
Showing results for 
Search instead for 
Did you mean: 

Backup of SQL Server does not truncate log file??

JDMils________
Level 4
I just completed a backup of our SQL server. The details are:
DB Name: JLRXAPP.mdf Size: 20GB
DB Log: JLRXAPP_Log.ldf Size: 45GB

The 45GB log file is still on the SQL Server. I thought it would be truncated after a full backup but it hasn't- why?

FYI, the backup was to the Backup Server's HDDs, and took 46 minutes.

SQL Server is V2000, BE V10.1 Rev 5629, Windows Server 2003 SP1
20 REPLIES 20

Keith_Langmead
Level 6
Assuming the 45GB is the file system size of the file, have you checked the amount of space used within that file by SQL after you've run a full backup?

If you look at the database through Enterprise Manager and go to the taskpad view it will show not only the physical size of the database files, but also the amount of space actually used. When the backup truncates the database I don't think it will actually shrink the physical file, instead stops removes the old data from the, and then re-uses that space when the file needs to grow again as more transactions are processed.

JDMils________
Level 4
I'm looking at the TaskPad view and it's telling me:

JLRXAPP DB = 20480MB (Used=17931.5MB, Free=2548.5MB)
JLRXAPP LOG = 44211.8MB (Used=41146MB, Free=3065.8MB)

Doesn't look like it's been truncated.

shweta_rege
Level 6
Hello,


The transaction logs will be deleted in two cases :

- If the database recovery model is Full and the transaction logs are backed up using the "Log" option.

- If the database recovery model is Simple.


Which Recovery Model did you select?



Thank You,

Shweta

JDMils________
Level 4
Looking at SQL Enterprise Manager, the Recovery model is set to FULL.

I recently placed the DB offline and deleted the log file. The log file has been growing ever since and is now at 15GB with successfull FULL backups every night.

<< ....and the transaction logs are backed up using the "Log" option.

Where do I find this Log option?

Can someone help figure out why the log file is not being truncated?

David_Sanz
Level 6
Partner
Hello

A SQL Server full backup does not truncate the log files. Only a Log backup (another type of SQL Server backup you can select) does it.

Regards

JDMils________
Level 4
Hi David,

Sorry for being away for so long. I've just noticed that the log file is now on 42 GB where the DB is sitting at 19 GB (TaskPad view).

>> A SQL Server full backup does not truncate the log files. Only a Log backup (another type of SQL Server backup you can select) does it.

I don't understand. We had configured SQL to backup the database to disk, then used NTBackup to backup the database which automatically (somehow) deleted the log file.

Why is Backup Exec different? I used to use BE on an Oracle DB and found that whenever a backup failed, the log file would grow (actually, Oracle would create defined sized files and just keep creating more files until a successful backup deleted them).

Can someone verify David's answer, that BE does not truncated SQL log files?

Hywel_Mallett
Level 6
Certified
I can verify what David says.
If you perform a "full" backup, the logfiles remain. You then have to perform a log backup to truncate the logs.
This is despite the fact that a "full" Exchange backup will truncate the logs.
I can't explain why BE is different for SQL backups...

Ken_Putnam
Level 6
It's been this way since v7.x and SQL 7.0

Even though the SQL backup utility will truncate logs after a "Full, Database and Logs" job, Backup Exec will only truncate with a "Logs, Trucate" job

JDMils________
Level 4
So can I configure BE to delete all DB log files only after a successful backup? There really is no need to backup a log file once the main DB is backed up- that's just duplication.

If this is possible, how do I set it up?

Bin_Fang_2
Level 4
I don�t think BE can delete the DB log files for you. If you choose the option to perform the SQL backup:

Log - Backup Transaction Log

it truncates the transaction log on successful completion of the backup. The size of the physical log file on the server remains the same. If you keep regularly the above backups, the size of the log file should not grow significantly unless you experience large data changes of your SQL DBs. One method of reducing the log file size on the SQL 2000 is the following:

Execute a SHRINKFILE statement for the log file interested

You may find more related info in the following link:

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

Ken_Putnam
Level 6
What we did was do a Full Database backup immediatetly followed by a Log File/Truncate job

JDMils________
Level 4
Guys,

How do I setup a Log Truncate job and how do I set it to run after the FULL DB backup completes?

Does the Truncate actually backup anything to tape or does it just do a truncation on the logs?

EDIT: Am I not right in saying that once a DB has been fully backed up, the Log files are then redundant? Logs are only changes in the DB since the last backup thus why would I want to do a FULL DB backup AND a Log backup? I'm confused!

JDMils________
Level 4
Can anyone make a suggestion here?

JDMils________
Level 4
Somebody MUST be doing SQL data backups using BEW!? And they must be truncating their logs automatically, somehow!?

I just need to know the steps required to truncate my log files after a successful backup. Is this too hard to do? Please help me.

Do the Symantec support guys frequent these forums?

Regards

JDMils

Hywel_Mallett
Level 6
Certified
As Ken says, a full backup followed by a transaction log backup seems to be the way to do this using BE. Yes it will result in you backing up the data twice, but at least the logs get truncated.

JDMils________
Level 4
Thanks Hywel, but how do I setup a transaction log backup to run after a full backup?

What I'm thinking is to do a FULL backup followed by a Transaction Log backup to disk, and only keep one copy of this.

Thanks

Alex_Davison
Level 3
We do. I have found that if you have the AOFO turned on this can cause the backup logs not to truncate properly.

Alex_Davison
Level 3
If you are just backing up the transaction logs immediately before, or immediately after a full backup, you may as well be running your database in Simple mode rather than Full Recovery mode.

If in the event of a complete hard drive failure or fire in the server room you are happy to just be able to restore from the last full database backup (each night I assume) then simple mode with a full backup each night will do you fine.

You should think of backing up the transaction logs as like doing an incremental backup of the database. With a transaction log backup happening every hour, you will be able to restore the last full backup and then every successful transaction log backup so that you data is restored to the latest hourly transaction log backup.

Alex

JDMils________
Level 4
Alex,

Thank you for your insight. I fully agree with you on this point. I know how to setup a FULL DB backup- that's no issue. My problem is how do I setup a Transaction Log backup to run immediately after the FULL DB backup has completed SUCCESSFULLY?

No one seems to be able to answer this question!