Clever Geek Handbook

Next log backup - will it contain any transactions?

I am trying to efficiently determine if a log backup will contain any data.

The best I have come up with the following:

DECLARE @last_lsn numeric(25,0)
SELECT @last_lsn = last_log_backup_lsn
    FROM sys.database_recovery_status WHERE database_id = DB_ID()
SELECT TOP 1 [Current LSN] FROM ::fn_dblog(@last_lsn, NULL)

The problem is that there are no transactions since the last backup, fn_dblog throws error 9003 with severity 20 (!!) and writes it to the ERRORLOG file and event log. This makes me nervous - I wish it would just not return any records.

FYI, I will make sure that I have hundreds of small databases that can run any time of the day but are usually used 8 hours a day. This means that 2/3 of my log backups are empty. These thousands of additional files can have a noticeable impact on the time required for both backups and disaster recovery.

+2


source to share


3 answers


I figured out an answer that works for my specific application. If I compare the results of the following two queries, I can determine if any action has taken place on the database since the last log backup.



SELECT MAX(backup_start_date) FROM msdb..backupset WHERE type = 'L' AND database_name = DB_NAME();

SELECT MAX(last_user_update) FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID() AND last_user_update IS NOT NULL;
+1


source


If I run

SELECT [Current LSN] FROM ::fn_dblog(null, NULL)


Seems to return my current LSN at the top, which matches the most recent log backup.

0


source


What happens if you change the selection from :: fn_dblog to count (*)? Does this fix the error?

If not, perhaps fetch the log entries in the temp table (top 100 from :: fn_dblog (null, NULL), ordering by date if any) and then query that.

0


source






More articles:


All Articles