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.
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?
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...
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:
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!
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.
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?