CODEWITHSUNDEEP
sql-serverbackup
Ted Pitts
Ted Pitts

Reputation: 31

Next log backup - will it contain any transactions?

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.

Upvotes: 2

Views: 312

Answers (3)

Ted Pitts
Ted Pitts

Reputation: 31

I figured out an answer that works for my particular application. If I compare the results of the following two queries, I can determine if any activity has occurred 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;

Upvotes: 1

GilaMonster
GilaMonster

Reputation: 1768

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.

Upvotes: 0

way0utwest
way0utwest

Reputation: 654

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.

Upvotes: 0

Related Questions