We have transaction log backups taken every few minutes from many databases. Is there a way to determine if there were any committed transactions since the last backup and only then do a log backup?

Some of the databases don't have any changes during the night, for example. But we'll still get many tiny log backups for these databases every time the log backup script runs.

I already tried scripting something based on the previous backup's last_lsn recorded in msdb and using fn_dblog to see if there were new committed transactions. I was not successful so far.

A bit of background:

These log backups are taken every few minutes and saved to Azure Blob Storage. They are not restored to another database (aka log shipping). During a single day we'll get a few hundred log backups per database. In case we ever have to restore something from Azure, I want to avoid having 100s or even 1000s of log files without any user transactions in them lengthening the restore process.

  • 2
    FYI: Restoring many log files is one of the many tasks of a SQL Server DBA. I highly recommend that you consider finding ways to automate it. – Mike Fal Apr 2 '14 at 19:20
  • 1
    It's automated, but takes very long. Removing a few thousand unnecessary HTTP requests will hopefully speed things up. – Josh W Apr 2 '14 at 19:24

One alternative here is to calculate the size of active transactions in your transaction log by using DBCC LOGINFO. Your logic would look something like this:

   RecoveryUnitID int
  ,  FileID      int
  , FileSize    bigint
  , StartOffset bigint
  , FSeqNo      bigint
  , [Status]    bigint
  , Parity      bigint
  , CreateLSN   numeric(38)

DECLARE @MBthreshhold int

SELECT @MBthreshhold = 5 --Enter your threshhold in MB here


IF (select sum(filesize/1024/1024) 
    from #vlfs
    where [Status] = 2) > @MBthreshhold
    BACKUP LOG [foo] to <<backup location>>

Please note there are a lot of caveats here. Your transaction log will never be completely empty and there is always something to backup to it. You can use this to determine at what size point you want to actually run your backup, but this can be a HUGE risk to your Recovery Point Objective (RPO). Your log backups will happen at variable times, meaning you can never guarantee the maximum amount of data loss you will incur in a disaster. This sort of strategy should be pursued with extreme caution.

  • I can't use a static number. I want to determine, in a long chain of log backups, if the next one is necessary or not - i.e. if there were any committed transactions since the last log backup. – Josh W Apr 2 '14 at 19:27
  • That's the thing, they're ALWAYS going to be necessary. There will always be something committed within the log, due to the nature of the log, how it functions, and internal SQL Server processes. – Mike Fal Apr 2 '14 at 19:29

I think if you have databases that you know have less (or especially no) activity during certain periods, you should simply back up the log less often during those periods. There is always going to be some log churn and determining whether it was due to user or system activity is going to be a nightmare (and there will almost always be some minute level of system activity, even on an idle system). If you want to increase your protection without creating a large number of log backups, you could consider taking FULL (and/or DIFF) backups more frequently.

In any case, you should not be designing your recovery strategy around the fact that you think reducing the number of http requests will speed things up. Your recovery time should be based mostly on the amount of data you're restoring overall, not the number of files or the number of http requests. And since restoring is hopefully a very rare event, you can probably picture that optimizing the thing you're doing all the time is more important than optimizing the thing you'll hopefully never have to do.

That all said, Paul Randal did write a stored procedure that can tell you how much data will be in your next backup - I think it seems prohibitively expensive to run this before every log backup, just to see if it's worth taking a backup, but hey - I can point you at a gun, and you know where your foot is. The rest is up to you. Rather than steal his code, I will just point you to it:


Will something like this help? It will tell you when the last transaction occurred and that's a start.

How to read the SQL Server Database Transaction Log

Are you planning on restoring those backups by hand or do you have a script that reads the file list and restores them in sequence? If the second option is your situation, I would suggest grabbing the file size and if it's 0, skipping the file.

  • 1
    You can't skip log files in a restore, they will never be completely empty. Skipping them means you break the log chain and your restore will fail. – Mike Fal Apr 2 '14 at 19:14
  • Correct. They contain some log records and are 640KB in size. – Josh W Apr 2 '14 at 19:18
  • That's what I thought but the OP said he had empty log files. – efesar Apr 2 '14 at 19:32
  • @efesar I think he meant that they didn't contain any user transactions, not that they were 0 KB. – Aaron Bertrand Apr 2 '14 at 19:40

What you can do is

1- do an initial backup followed by an DBCC SHRINKFILE

BACKUP LOG DatabaseName TO DISK = N'D:\Backup\DatabaseName_log.bak'
DBCC SHRINKFILE('MyDatabase_Log', 1)

2- Before running the backup run a check to see if the transaction log size increased from the 1 MB size we shrieked it to ;
If is bigger then 1 MB then you backup it.
Se script to check this :

SELECT (s.size * 8.0)/1024.0 AS size_in_mb FROM sys.master_files s
INNER JOIN sys.databases d
on s.database_id=d.database_id
WHERE d.name = 'db_name' and s.type_desc='LOG';
  • 4
    I really don't recommend this method at all, for a variety of reasons. – Aaron Bertrand Apr 2 '14 at 19:23
  • Fell free to put out your reasons - were all here to exchange knowledge and doubts – Up_One Apr 2 '14 at 19:25
  • What happens after the first log backup? How will the script determine when to do the second log backup? – Josh W Apr 2 '14 at 19:26
  • Well one is that constantly shrinking and growing the log file is expensive. The second is that you're assuming that only transactions larger than 1 MB (or that push the log out beyond 1 MB, at least) are important. – Aaron Bertrand Apr 2 '14 at 19:27
  • 1
    No, this is still a bad idea. – Aaron Bertrand Apr 2 '14 at 19:31

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.