0

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.

Improve this question
3
  • 1
    If you are on sql server 2017, you can use Select log_since_last_log_backup_mb from sys.dm_db_log_stats(DB_ID()) (smart backup) – Sabin B Mar 11 '18 at 18:29
  • There are some cases when a select can cause a database to change. see : sqlskills.com/blogs/paul/… – Sabin B Mar 11 '18 at 18:37
  • 1
    I feel like adding this complexity will cause way more overhead and possibility of failure than you will save by not taking a log backup once in a while. – Aaron Bertrand Mar 11 '18 at 18:57
2

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.

Improve this answer

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.