Sql-server – Only do log backup if necessary in SQL Server

backupsql servertransaction-log

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.

Best Answer

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:

CREATE TABLE #vlfs(
   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

INSERT INTO #vlfs
EXEC ('DBCC LOGINFO')

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

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.

Related Question