Sql-server – Is conditionally SQL Server log backup possible

backupsql servertransaction-log

I have a SQL Server database and right now we have weekly full backup, daily differential backup and 5 minutes log backup. Most of the time there are reads and writes happening in the database. Sometimes, for example there was continuously eight hours period yesterday, there was only read operations happened in the database and no writes. In this period I don't want to do the 5 minutes log backup as there isn't any modifications happened. Is there a way for me to test whether some modifications occurred in the database so that I can actually take the log backup, otherwise the log backup session will just skip it. Is this idea reasonable and doable? I simply don't want to have too many log backup files which makes it a little bit harder to manage.

Edit:

I considered the function fn_dblog. My idea is to count(*) the result of this function and if it increased then I'll take a log backup. But I'm not sure whether read will cause log record increase.

Best Answer

My idea is to count(*) the result of this function and if it increased then I'll take a log backup. But I'm not sure whether read will cause log record increase.

A read action, including reading from the system management views/functions should not cause any log activity, so this would work as you intend.

But why do you need to skip the log backups in this case? They will be minimally small as there has been no new activity since the last log backup so won't consume significant resources to take, store, transfer, and (if needed later) restore, and you would be adding an extra complication (so a potential point of failure) into your process.

Related Solutions

Sql-server – Backup stored procedure for full, differential and log backup

There are quite a few solutions to have your daily backups (full, diff and logs). You could:

Write your own script: it might be useful if you need a fine control on it, or if you need to respect some naming convention. I did not test it, but your code looks reasonnable.
Use the maintenance plans: very easy to set up, but less control. This might be a good solution in your case.
Use a 3rd party script / tool: Ola Hallengren's scripts are very good, but you can find plenty of others.

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

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.

Best Answer

Related Solutions

Sql-server – Backup stored procedure for full, differential and log backup

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

Related Question