2

Today I noticed that the backup of the log of our database was 18GB but the size of the log file is only 8GB.

I used the following command to backup:

BACKUP LOG [myDB] 
TO DISK = N'g:\sqlbackup\LOG\myDB_LOG_20160329_084501.trn' 
WITH CHECKSUM
    , COMPRESSION;

Can anyone explain how it's possible that the backupfile is larger than the logfile?

Update:

The database is in full recovery mode and here are 216 VLF's in my log file.
I just looked to the size of the ldf file to determine the size of the log (I know this is not the right way but you would expect if the backup is 18GB the ldf file would be greater than 8GB).

I asked my colleague and he shrunk the log file manually before backup, the size changed from 10GB to 8GB (only 2% of the logfile was being used).

Why would a shrink (after or before backup) of the log file cause this? Also there is only one backup in my backup file.

Improve this question
3
  • 1
    Is it possible the log file was manually shrunk (or some automated process did so) after the backup took place?
    – Aaron Bertrand
    Commented Mar 29, 2016 at 15:05
  • How did you get your log size? Use DBCC SQLPERF(logspace) to get the log size and % used to estimate the approx backup size. I'm not aware that it could be wildly different, unless as Aaron says, the log file was shrunk after backup.
    – Molenpad
    Commented Mar 29, 2016 at 15:06
  • 1
    How many VLFs do you have in the log file? You can check the numbwer of VLFs via: USE [DbName]; DBCC LOGINFO ... you'll get one row per VLF.
    – AMtwo
    Commented Mar 29, 2016 at 15:10

1 Answer 1

Reset to default
3

Your T-SQL BACKUP LOG command does not use the INIT option. If you do not specify the INIT option, and the file target backup file already contains one or more backups, the backup you are taking is appended to the end of the file.

This would explain how your log backup seems larger than the log it backed up.

You can tell if there is more than one backup in the target file by looking at the results of:

RESTORE HEADERONLY 
FROM DISK =  N'g:\sqlbackup\LOG\myDB_LOG_20160329_084501.trn';

See the "Media Set Options" in the Backup MSDN page.

Improve this answer
2
  • 1
    While this is a potential cause, since a timestamp from today is in the command, it's probably only possible in the case where the command was extracted and run manually multiple times. If the filename is normally generated through automation, this would be an edge case (but I do agree, you should always use INIT if you want a timestamp in the filename).
    – Aaron Bertrand
    Commented Mar 29, 2016 at 20:57
  • agreed, Aaron - I thought from the symptoms it seemed, along with your suggestion about the shrink, one of the most likely explanations.
    – Hannah Vernon ♦
    Commented Mar 29, 2016 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.