Next log backup - will it contain any transactions?

Question

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

The best I have come up with is 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 when there are no transactions since the last backup, fn_dblog throws error 9003 with severity 20(!!) and logs it to the ERRORLOG file and event log. That makes me nervous -- I wish it just returned no records.

FYI, the reason I care is I have hundreds of small databases that can have activity at any time of day, but are typically used 8 hours/day. That means 2/3 of my log backups are empty. Those extra thousands of files can have a measurable impact on the time required for both off-site backup and recovering from a disaster.

Answer 1

If I run

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

It seems to return my current LSN at the top that matches the last log backup.

Answer 2

What happens if you change the select from ::fn_dblog to a count(*)? Does that eliminate the error?

If not, maybe select the log records into a temp table (top 100 from ::fn_dblog(null, NULL), ordering by a date, if there is one) and then query that.