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.
Select log_since_last_log_backup_mb from sys.dm_db_log_stats(DB_ID())
(smart backup) – Sabin Bio Mar 11 '18 at 18:29