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.

  • 1
    Is it possible the log file was manually shrunk (or some automated process did so) after the backup took place? – Aaron Bertrand Mar 29 '16 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 Mar 29 '16 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 Mar 29 '16 at 15:10
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.

  • 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 Mar 29 '16 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. – Max Vernon Mar 29 '16 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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