Only do log backup if necessary in SQL Server

Question

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.

• Mike Fal: 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.
• Josh W: It's automated, but takes very long. Removing a few thousand unnecessary HTTP requests will hopefully speed things up.
• The incredible Jan: @JoshW Really sad that your account was deleted. Would have been interesting if you found a solution.

Answer 1

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.

• Josh W: 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.
• Mike Fal: 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.
• The incredible Jan: @MikeFal Nobody cares about "internal SQL Server processes". They have nothing to do with transactions. They don't change any data.

Answer 2

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:

• New script: how much data will the next log backup include?
  _{I don't know if this has been tested on 2012/2014/Azure.}

Answer 3

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.

• Mike Fal: 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.
• Josh W: Correct. They contain some log records and are 640KB in size.
• efesar: That's what I thought but the OP said he had empty log files.
• Aaron Bertrand: @efesar I think he meant that they didn't contain any user transactions, not that they were 0 KB.

Answer 4

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'
GO
DBCC SHRINKFILE('MyDatabase_Log', 1)
GO

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';

• Aaron Bertrand: I really don't recommend this method at all, for a variety of reasons.
• Up_One: Fell free to put out your reasons - were all here to exchange knowledge and doubts
• Josh W: What happens after the first log backup? How will the script determine when to do the second log backup?
• Aaron Bertrand: 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: No, this is still a bad idea.