What Do You Look for in an Execution Plan?

Say, you have a query having performance issues and you decide to look it's graphical execution plan in SSMS (or other tools). If you are lucky, right off the bat SQL Server reveals a quick and easy fix:

Its missing a crucial index! You add the missing index and the problem solved, you are the hero of the hour!!!!

Sooner than later, you will come across queries that don't have such obvious fixes aka low hanging fruits.  And, you will realize, analyzing execution plans is very tedious, time consuming and its probably impossible to read every single property of every single node of every single query.  You decide to be smarter about it so you apply the pareto principle 1) focus your energies on 20% queries causing 80% of performance issues (or maybe there is no performance issue to solve per se but you just want to make things run better) and 2) Within execution plans for those 20% of queries, you devote your attention to the highest impact or informative operators of the execution plans.  For example:

Plan metadata:

Start by first reviewing the plan metadata. It is the very first operator listed on the far left of the plan, it contains the metadata about the plan and as you can probably see in this screen shot, the information displayed is at least, informative:

At the bottom you see the “Warnings” that are associated with this plan, all in one place.

Warnings:

Warnings are indicators of a possible issue with the query that could affect the performance. This doesn't necessarily mean that its a real problem. It's just an indicator of a possible problem and should be investigated.

The tooltip will show the reason for the warning:

In that particular case if I am not using this column in a where clause, then this is most likely a false warning as it cannot then affect the row count estimates. Whereas in the following example both queries show warning in the execution plan due to the use of the CAST function. However its the second query where it's negatively affecting the performance because the war due to the use of CAST function in the WHERE CONDITION, making it essentially unsargable.  First you will notice that the second query is switched to doing scan vs seek operator being used by the first query. 

And secondly,  the second query requires significantly (relatively speaking) more I/O to get the same results. 

(1 row affected)

Table 'TBL_______________'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 67, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

(1 row affected)

Table 'TBL_______________'. Scan count 1, logical reads 1367, physical reads 0, read-ahead reads 0, lob logical reads 69, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

Most Costly Operations:

Even though the costs listed are estimates only, optimizer does rely on those estimates and therefore we must too pay more attention to them.

Fat Pipes:

Pipes, are the arrows that connect one operator to the next and represents data movement. Width of the pipes indicate amount of data being processed so a thick pipe would indicate lots of data and thin would mean less data. Another thing you would want to pay attention to is the change in pipes width from thin to fat or from fat to thin or, a fat pipe getting even fatter.

Scans: 

Relatively speaking, seeks are better than scans. It’s not that Seeks are always good or that scans are always bad but scans do indicate I/O which is often an issue for query performance.  Some times optimizer will choose scan even if a seek option is available and often it's right. In a simplistic example,  if my query SELECT * FROM MYTABLE; that contains no WHERE clause or any kind of filtering condition at all, in that case the optimizer can only choose a scan,  In fact for such queries a seek will run slower than a scan.

In short, look at the scan operators more closely than say seek operators. 

Huge discrepancies between estimated and actual counts

For example, in the below screenshots the estimated rows is only 8 but actual plan returned 5.1 million rows, that's a huge discrepancy.

This could be result of 1) Outdated Statistics ( Do you have outdated statistics?

2) Parameter sniffing 3) a bad query plan getting cached (use WITH RECOMPILE to get rid of it) or sometimes combination of more than one factors.

Another discrepancy to pay attention is to look if there is a very high “Estimated Number Of Rows to be Read” compared to very low “Estimated Rows Per Execution”.  This maybe indication that your WHERE clause is unsargable.   A very simple example it would be to use aggregate functions (sum, count etc.) against all rows. 

Final thoughts:

Often this approach works to quickly identify query performance problems. Even if it doesn't eventually, its a good starting strategy. And I would admit this has its limits and won't always be enough or "good enough"  and you do then have to put in real hard work in understanding and optimizing your query. 

At that point its good idea to start paying attention to the the NodeIDs of each operation. NodeIDs are integer values starting with 0...n, from left-to-right.  It lets you know order in which your query is being processed by the SQL Server...   Some operators even refer back to other operators. 

Next, keep track of output of each operator, some operators add or change columns to the query results so knowing where each column came from can be very helpful. 

And finally, to get a complete picture, get into habit of reviewing properties of each operator and not just look at the information in the tooltip. 

Find out how fast is your SQL database growing using the backup metadata from msdb

Often, we need to determine the rate of growth of a database or identify which database is expanding the fastest among all those on a SQL instance. Currently, I do not have a central repository where I regularly poll and store the database size or other data. Nor do I have a CMDB tool at my current job.

What I present here is an ad-hoc, indirect method to gauge database growth using backup history information from the MSDB database. This script should be used primarily for ad-hoc solutions, though I find it a convenient and quick method when I need to quickly assess growth.

It’s important to note that this script will not be helpful for databases that are not backed up (there may be justifiable reasons for this) or if you only keep backup history in MSDB for a very short period of time (e.g., purging them after 7 days).

THIS IS A SCRIPT SO PLEASE REMEMBER TO MODIFY THE VALUES FOR 

THE FOLLOWING VARIABLES BEFORE RUNNING IT:

declare @backup_start_date datetime  -- Example: '7/1/2022'
declare @backup_end_date datetime    -- Example: '8/1/2022' 

                                     -- default is current date/time 
declare @number_of_days int = 30  -- ignored if the @backup_start_date is given

 

/*
GET THE DATABASE SIZE GROWTH INDIRECTLY BY COMPARING CHANGE IN 

DATABASE BACKUP SIZE

You can either specify a date range or a number of days to go 

back to compare with. For example, you can specify a date range 

if you want to know how much your database grew during a specific 

period in the past. This info can be useful for comparison between 

two different time periods.

And specify a number of days if you want to know much the database 

grew in the past week, month, year, etc.

Uses the backup metadata from the msdb to get the backup size. 

If you are like me, you probably keep the msdb size in check by 

purging metadata regularly. So, you  can only go as far back as 

you have the backup metadata for.

Limitations:
In the case of an AG cluster, you must run the query on the 

replica where the full backups are performed or run it as a 

multi-server query against all nodes simultaneously (recommended).

Only looks for the full/complete backups (backup type=D). 

In other words, does not consider differential and/or log backups.
*/

-- Check if the temp table already exists and drop if it does
if OBJECT_ID('tempdb..#db_backup_metadata') is not null 
    drop table #db_backup_metadata

go

-- LET'S FIRST GET THE DATA FROM msdb..backupset INTO A TEMP TABLE
declare @backup_start_date datetime -- Example: '7/1/2022'
declare @backup_end_date datetime   -- Example: '8/1/2022'    
                                    -- default is current date/time 

-- ignored if the @backup_start_date is given

declare @number_of_days int = 90    

-- Error handling for date input
if @backup_start_date is null and @number_of_days is null
begin
    raiserror('Error: Either a start date or number of days to go back 

to must be provided.', 16,1)
    return
end

-- Set default values for start and end dates
set @backup_start_date = isnull(@backup_start_date, GETDATE() - 

                         @number_of_days)

set @backup_end_date   = isnull(@backup_end_date, GETDATE())

-- Printing the start and end dates
print '-- BEGIN AND START DATES:'
print @backup_start_date
print @backup_end_date
print '--'

-- Error handling for start and end date logic
if @backup_end_date < @backup_start_date 
begin
    raiserror('Error: Start date cannot be after the end date.', 16,1)
    return
end

-- Select and insert backup data into temp table
SELECT 
    bs.backup_set_id,
    bs.database_name,
    bs.database_creation_date db_create_date,
    bs.backup_start_date,
    cast(bs.backup_size / 1024 / 1024 / 1024 as numeric(19,3)) 

        backup_size_gb
INTO #db_backup_metadata
FROM msdb..backupset bs 
WHERE
    bs.type = 'D' 
    and bs.backup_start_date >= @backup_start_date
    and bs.backup_finish_date <= @backup_end_date
ORDER BY database_name, backup_start_date DESC

-- USE THE CTEs TO GET THE DESIRED DATA
;WITH c1 AS
(
    SELECT database_name, 
           db_create_date, 
           backup_start_date, 
           backup_size_gb 
    FROM #db_backup_metadata a
    WHERE backup_set_id = 
        (SELECT MIN(backup_set_id) 
         FROM #db_backup_metadata 
         WHERE database_name = a.database_name 
         GROUP BY database_name)
),
c2 AS
(
    SELECT database_name, 
           backup_start_date, 
           backup_size_gb 
    FROM #db_backup_metadata a
    WHERE backup_set_id = 
        (SELECT MAX(backup_set_id) 
         FROM #db_backup_metadata 
         WHERE database_name = a.database_name 
         GROUP BY database_name)
)

-- Final select statement to output the growth data
SELECT
    dbs.name db_name,
    c1.db_create_date,
    c1.backup_start_date backup_date1, -- earliest backup
    c2.backup_start_date backup_date2, -- most recent backup
    datediff(D, c1.backup_start_date, c2.backup_start_date) [days],
    c1.backup_size_gb backup_size1,    -- size of the earliest backup
    c2.backup_size_gb backup_size2,    -- size of the most recent backup
    (c1.backup_size_gb - c2.backup_size_gb) * -1 growth_gb,
    try_cast(case when c1.backup_size_gb > 0 then
        ((c1.backup_size_gb - c2.backup_size_gb) * -1) * 100 
        / c1.backup_size_gb 
        else 0 end as numeric(10,2)) growth_rate
FROM sys.databases dbs
LEFT JOIN c1 ON dbs.name = c1.database_name
LEFT JOIN c2 ON c1.database_name = c2.database_name
WHERE dbs.name != 'tempdb'
ORDER BY growth_gb DESC

Now, I'm curious to hear from you! How do you currently track your SQL database growth, and how effective has it been? Or, if you've tried out the method described in this post, I’d love to hear about your experiences. Did it offer new insights into your database management? 

Monitor SQL replication subscriptions synchronization status

In your current role if you are managing a SQL server replication setup, you are probably well aware that if any of the subscriber is not getting synchronized within a reasonable time (which is subjective and variable, I may add), it has serious consequences not only for the subscribers but also for the publisher.

So I had written this little script to see if any of the subscriber had not been synchronized in last @hrs. I used 24 hours as a threshold as some of the subscriptions we only synchronized after hours. So adjust that value according to your needs.

Caveat: I wrote this script long time ago to monitor and get alert if any of the replication subscribers were falling behind or had completely stopped getting synchronized with the publisher. So its not up to date with any new features etc. that newer versions of SQL Server may have added to the replication technology. For example there maybe now built in alerts you could leverage to achieve the same results. The built alerts were probably available even back then as well but I had found my little custom script much more suitable for our needs at that time. So I am hoping you may find this just as useful or give a starting point for your own solution.

RUN THIS SCRIPT ON THE DISTRIBUTOR SERVER

/* Execute this script on the distributor server
Description: Return subscriptions that have not been synchronized in last @hrs hours.

The script loops through each publisher served by the local distributor server.

*/
SET NOCOUNT ON
-- declare variables
DECLARE @SQL NVARCHAR(4000), @SERVERNAME VARCHAR(500), @mypublisher nvarchar(500), @hrs int
DECLARE @rcpts  nvarchar(1000), @msg  nvarchar(1000), @sub nvarchar(150), @query2 nvarchar(2000)

SET @hrs = 24 -- return subscription that has not synchronized in last @hrs
SET @rcpts = '<YOUR EMAIL ADDRESS>'


-- make sure the distribution server is configured for the data access 
if not exists (select * from sysservers where srvid = 0 and dataaccess = 1)
	EXEC('exec sp_serveroption @server = ''' + @@SERVERNAME + ''', @optname = ''DATA ACCESS'', @optvalue = ''TRUE''')

-- uses a cursor to loop through each publisher
DECLARE C1 CURSOR FOR SELECT NAME FROM MSDB..MSdistpublishers ORDER BY 1
OPEN C1
FETCH C1 INTO @mypublisher

WHILE @@FETCH_STATUS = 0
BEGIN
	IF OBJECT_ID('TEMPDB..##Temp_sp_replmonitorhelpsubscription') IS NOT NULL
	   DROP TABLE ##Temp_sp_replmonitorhelpsubscription

	SET @SQL = 'select * INTO ##Temp_sp_replmonitorhelpsubscription from openquery([' + @@SERVERNAME  + '], ''SET FMTONLY OFF exec [distribution].sys.sp_replmonitorhelpsubscription @publisher = ''''' + @mypublisher + ''''', @publication_type = 0, @mode = 0, @exclude_anonymous = 0, @refreshpolicy = N''''0'''''')'

	PRINT @SQL
	EXEC (@SQL)
	IF EXISTS (SELECT @mypublisher Publisher, * FROM ##Temp_sp_replmonitorhelpsubscription WHERE datepart(hh, getdate()-last_distsync) > @hrs)
	BEGIN
		SELECT @mypublisher Publisher, * FROM ##Temp_sp_replmonitorhelpsubscription WHERE datepart(hh, getdate()-last_distsync) > @hrs
--		SELECT @mypublisher Publisher,Subscriber, Publication FROM ##Temp_sp_replmonitorhelpsubscription WHERE datepart(hh, getdate()-last_distsync) > @hrs
		SET @sub = @mypublisher + ': Subscribers found with no data sync in last ' + cast(@hrs as varchar(15)) + ' hours'
		SET @msg = ''
		SET @query2 = 'SET NOCOUNT ON; SELECT ''' + LEFT(@mypublisher, 25) + ''' Publisher,LEFT(Subscriber, 15) Subscriber, LEFT(Publication, 35) Publication, last_distsync [LastDistSync] FROM ##Temp_sp_replmonitorhelpsubscription WHERE datepart(hh, getdate()-last_distsync) > ' + cast(@hrs as varchar(15)) 
		EXEC dba..sp_sendmail 
			@recipients = @rcpts  
			,@message = @msg  
			,@subject = @sub  
--			,@body_format = 'HTML'
			,@query = @query2  

		
	END	
	FETCH C1 INTO @mypublisher
END

CLOSE C1
DEALLOCATE C1

 .

Truncating very large transaction log in full recovery mode

While I always configure transaction log backups for every database in non-simple recovery model, and to compliment it I also have monitoring in place if transaction log doesn't get backed up, which does happen on rare occasions. For instance in my current organization where all applications/databases are third party apps with no in-house developed apps, once in a while an app/user may run a huge transaction, it could be that they decided to purge almost all historical data from a 500TB database and delete 450TB data from it all in a single transaction, leaving a huge transaction log and not enough available disk space on the backup drive (separate issue/topic).     

Per best practice and for almost any conceivable argument, its preferred that you take a transaction log backup and then shrink the log file/s. But in an emergency situation you could use the following SQL script to quickly transaction the transaction log.  I believe the script is self-explanatory but I missed anything or have questions, please feel free to post a comment here and I will go my best to get to back you. 

/* CAUTION:
USE THIS ONLY IN EMERGENCY WHERE THE TRANSACTION LOG HAS NOT BEEN BACKED 

IN A VERY LONG TIME AND HAS GROWN TO BE TOO BIG

The script does the following in order to shrink the transaction log file:
- Ensure the db is not running in Simple recovery mode
- Store the database recovery mode into a variable
- Change recovery mode to SIMPLE
- Issues checkpoint command
- Wait 2 seconds
- Shrink the log file
- Wait 2 seconds
- Change recovery mode back to original value

IMPORTANT: This script does not take a full or differential database backup so its crucial that you take a full or differential backup to re-set the transaction log chain. Otherwise any attempt to take a transaction log backup will fail, creating more problems.

*/

declare @db nvarchar(100) = 'MyDB'               -- Specify name of the database here
declare @target_log_size_mb nvarchar(20) = 64    -- if DB has multiple log files, each log file will be of this size
declare @recovery_mode nvarchar(100)
declare @sql nvarchar(4000)

select @recovery_mode = recovery_model_desc from sys.databases where name = @db

if @recovery_mode='SIMPLE'
   raiserror('Error: Use this script only on database in Full recovery mode.', 16, 1)
else if databasepropertyex(@db, 'Updateability')='READ_ONLY'
   raiserror('Error: Database is not updatable. Please ensure that the database is not read only, mirrored or otherwise inaccessible.', 16, 1)
else
BEGIN
		set @sql = '
		USE [master]
		ALTER DATABASE [' + @db + '] SET RECOVERY SIMPLE WITH NO_WAIT
		USE [' + @db + ']
		WAITFOR DELAY ''00:00:02''
		checkpoint
		WAITFOR DELAY ''00:00:02''

		-- open a cursor so we can iterate through and shrink all log files
		declare @log_file_name sysname
                declare c1 cursor for
		select name from sys.database_files where type=1
		open c1
		fetch c1 into @log_file_name
		while @@FETCH_STATUS = 0
		begin
			   print @log_file_name
			   DBCC SHRINKFILE (@log_file_name, ' + @target_log_size_mb +')
			   fetch c1 into @log_file_name
		end
		close c1
		deallocate c1

		WAITFOR DELAY ''00:00:02''
		USE [master]
		ALTER DATABASE [' + @db + '] SET RECOVERY ' + @recovery_mode + ' WITH NO_WAIT
		'
		print @sql
		exec sp_executesql @sql
END

For database participating in an AlwaysOn Availability Group:

/* CAUTION:
USE THIS ONLY IN EMERGENCY WHERE THE TRANSACTION LOG HAS 
NOT BEEN BACKED IN A VERY LONG TIME AND HAS GROWN TO BE TOO BIG

This script generates a TSQL script that does the 
following in order to shrink the transaction log file:

- Ensure the db is not running in Simple recovery mode
- If the DB is participating in an Availablity Group then
  ensure that we are on the primary replica server

- Store the database recovery mode into a variable
- Store the Availability Group name into a variable
- Change recovery mode to SIMPLE
- Issues checkpoint command
- Wait x number of seconds
- Shrink the log file/s
- Wait x number of seconds
- Change recovery mode back to original value

- AG settings, If applicable
  Backup the database 
  Add the database back to the AG
  Provide TSQL to drop the database on secondary replicas

WHAT IT DOESN'T DO:

- It won't delete/drop the database on the secondary replicas

*/

-- Specify the database name here

DECLARE @db nvarchar(100)  = 'VLDB';

-- If DB has multiple log files, each log file will be of this size
DECLARE @target_log_size_mb nvarchar(20) = 64;    

DECLARE @recovery_mode nvarchar(100);
DECLARE @sql nvarchar(4000);
DECLARE @ag_name nvarchar(100);
DECLARE @is_primary_replica int;

SELECT
		@recovery_mode      = recovery_model_desc, 
		@is_primary_replica = agrs.is_primary_replica, 
		@ag_name            = ag.name

FROM sys.databases d
left join sys.dm_hadr_database_replica_states agrs
ON d.database_id = agrs.database_id and agrs.is_local = 1
left join sys.availability_groups ag
ON ag.group_id = agrs.group_id
WHERE d.name =  @db;


IF DB_ID(@db) IS NULL
	RAISERROR('No database found with name Database %s.', 16, 1, @db);
ELSE IF @recovery_mode='SIMPLE'
	RAISERROR('Error: Use this script only on database in Full recovery mode.', 16, 1);
ELSE IF databasepropertyex(@db, 'Updateability')='READ_ONLY'
	RAISERROR('Error: Database is not updatable. Please ensure that the database is not read only, mirrored or otherwise inaccessible.', 16, 1);
ELSE IF @ag_name is not null and @is_primary_replica = 0
BEGIN
	RAISERROR('Database %s is participating in an AlwaysOn Availablity Group.', 16, 1, @db);
	RAISERROR('This must be run on the primary replica server.', 16, 1);

END
ELSE IF @ag_name is null
BEGIN
	RAISERROR('Database %s is not part of an AlwaysOn Availablity Group.', 16, 1, @db);
END

ELSE
BEGIN
		SET @sql ='';
		IF  @ag_name is not null and @is_primary_replica = 1
		SET @sql = @sql + '
		/* EXECUTE ON THE PRIMARY REPLICA SERVER */

		RAISERROR(''INFO: Removing database [%s] from Availability Group [%s]...'', 10, 1, ''' + @db + ''', ''' + @ag_name +''')  WITH NOWAIT;

		USE [master]
		ALTER AVAILABILITY GROUP [' + @ag_name + '] REMOVE DATABASE [' + @db + '];'

		SET @sql = @sql  + '
		RAISERROR(''INFO: Change database recovery model to SIMPLE....'', 10, 1) WITH NOWAIT
		USE [master];
		ALTER DATABASE [' + @db + '] SET RECOVERY SIMPLE WITH NO_WAIT;
		USE [' + @db + '];
		WAITFOR DELAY ''00:00:01''
		CHECKPOINT;		WAITFOR DELAY ''00:00:01'';

		-- open a cursor so we can iterate through and shrink all log files
		RAISERROR(''INFO: Truncate all transaction log files.....'', 10, 1) WITH NOWAIT;
		DECLARE @log_file_name sysname;
          DECLARE c1 cursor for
		SELECT name from sys.database_files where type=1;
		OPEN c1;
		FETCH c1 into @log_file_name;
		WHILE @@FETCH_STATUS = 0
		BEGIN
----			   PRINT @log_file_name;
			   RAISERROR(''INFO: Truncating transaction log file %s....'', 10, 1, @log_file_name); 
			   DBCC SHRINKFILE (@log_file_name, ' + @target_log_size_mb +');
			   FETCH c1 into @log_file_name;
		end
		CLOSE c1;
		DEALLOCATE c1;

		WAITFOR DELAY ''00:00:01'';
		RAISERROR(''INFO: Change back the database recovery model to %s...'', 10, 1, ''' + @recovery_mode +''') WITH NOWAIT
		USE [master];
		ALTER DATABASE [' + @db + '] SET RECOVERY ' + @recovery_mode + ' WITH NO_WAIT;

		'
		SET @sql = @sql  + '

		RAISERROR(''INFO: Backup the database...'', 10, 1, ''' + ''') WITH NOWAIT

		BACKUP DATABASE [' + @db + ']  
		TO  DISK = N''O:\' + @db + '_' + CONVERT(nvarchar(10), getdate(), 112) + '.bak'' 
		WITH NOFORMAT, 
			 INIT,  
			 COMPRESSION,  STATS = 10;

		RAISERROR(''INFO: Add the database back to the availability group....'', 10, 1) WITH NOWAIT;
		ALTER AVAILABILITY GROUP [' + @ag_name + '] ADD DATABASE [' + @db + '];

		/* EXECUTE ON THE SECONDARY REPLICAS

		-- DROP THE DATABASE ON THE SECONDARY REPLICAS
		USE MASTER; ALTER AVAILABILITY GROUP [' + @ag_name + '] GRANT CREATE ANY DATABASE;
		
		IF DB_ID(''' + @db + ''') IS NULL
			RAISERROR(''No database found with name Database %s.'', 16, 1, ''' + @db + ''');
		ELSE IF EXISTS (SELECT * FROM SYS. DATABASES
             WHERE NAME = ''' + @db + '''
			   AND STATE_DESC = ''RESTORING'')
          BEGIN
			PRINT ''-- Dropping database: ' + @db + '''
			DROP DATABASE [' + @db + ']
		END
		ELSE
			RAISERROR(''Error: Cannot drop database %s. Ensure that it exists and is not in RESTORING state.'', 16, 1, ''' + @db + ''');


		*/
		'
		PRINT @sql;
END;

Obviously, if a DBA or a more experienced Database Developer is the one running a gigantic transaction (e.g. delete 450TB worth of data from a 500TB database), they would most likely switch the recovery mode to SIMPLE and disable the transaction log backups BEFORE running such a transaction, then change it back to FULL/Bulk-Load mode, take a full backup and re-enable transaction log backup job.

They would also likely to break such operations into a much smaller, manageable batches/transactions.

Monitor SQL Server Transaction Log Backups Across Always On Replicas

This post applies to SQL Server 2016 and later, where newer DMVs make it easier to inspect transaction log activity across databases and replicas.

For years I relied on the backup history tables in msdb to check whether backups were running on schedule and staying within SLAs. There are, of course, other ways to monitor backups, but I prefer having a simple, query‑driven view that I can trust.

I don’t like taking chances with database backups, not just for disaster recovery but also for day‑to‑day availability. If transaction log backups are skipped due to oversight or repeated job failures, the log can grow until it fills the underlying disk, potentially taking not only that database offline but any others sharing the same volume.

To avoid that, I use a script that checks when the last successful transaction log backup completed and compares it to a threshold (for example, more than 24 hours). If that threshold is exceeded, I get an automatic email alert.

Things get even more interesting once you add Availability Groups into the mix, especially if your organization uses third-party backup tools. SQL Server allows transaction log backups to run on any eligible replica rather than forcing them onto the primary. That flexibility is great for offloading work, but it also means you need to know which replica can back up which database types.

Here is a quick summary of which backup types are allowed on each replica type.

Backup Type	Primary Replica	Secondary Replica (Synchronous and Asynchronous)
Full	Yes	Yes but with Copy_Only option)
Log	Yes	Yes (we cannot take log backup with COPY_ONLY on secondary replica)
Differential	Yes	No

Backup events are recorded only on the replica where the backup actually occurs, and that data doesn't automatically sync across replicas.

That makes checking for the last successful backup more complicated than it first appears.

If you always run backups from one specific replica, you can usually rely on msdb's backup history tables. But even then, you'll eventually face some unpleasant surprises (are there any other kind?).

To cover all the bases, I needed a more reliable approach. Luckily, Microsoft introduced the sys.dm_db_log_stats function in SQL Server 2016 and later. This DMV has fields that get updated regardless of whether you're on the primary or secondary replica, including log_backup_time, which shows the timestamp of the last successful log backup.

Here's the query I use to monitor transaction log backups across all replicas.

SELECT 
    d.name AS [Database],
    dbl.recovery_model,
    dbl.log_backup_time,
    dbl.total_log_size_mb,
    dbl.log_since_last_log_backup_mb,
    dbl.log_since_last_checkpoint_mb,
    dbl.total_vlf_count,
    dbl.active_vlf_count,
    dbl.active_log_size_mb,
    dbl.log_truncation_holdup_reason,
    dbl.log_state
FROM 
    sys.databases d
CROSS APPLY 
    sys.dm_db_log_stats(d.database_id) dbl;

Partial result:

Here is an example of one of its use cases:

--===============================================================================
-- TRANSACTION LOG BACKUP CHECK AND EXECUTION SCRIPT
-- Purpose: Automatically check database transaction log backup status and trigger
-- backup if older than 24 hours. Designed for automated maintenance jobs.
-- Author: DBA Team
-- Last Updated: Jan 16, 2026
--===============================================================================

DECLARE @DBName sysname = 'msdb';  -- Target database
DECLARE @DBID int = DB_ID(@DBName); -- Get database ID for DMV query efficiency

-- Validate database exists
IF @DBID IS NULL
BEGIN
    RAISERROR('ERROR: Database %s does not exist or DB_ID lookup failed.', 16, 1, @DBName);
    RETURN;
END;

-- Check last transaction log backup time using dynamic management view
DECLARE @LogBackupTime datetime = (
    SELECT log_backup_time 
    FROM sys.dm_db_log_stats(@DBID)  -- Returns last log backup timestamp
);


-- Main logic: Trigger backup if log backup is older than 24 hours
IF @LogBackupTime < DATEADD(DAY, -1, GETDATE())  -- Explicit 24hr threshold
BEGIN
    DECLARE @SQL NVARCHAR(4000);
    SET @SQL = N'BACKUP LOG ' + QUOTENAME(@DBName) + 
               N' TO DISK = N''C:\' + @DBName + N'.TRN''' +
               N' WITH INIT, COMPRESSION';
    
    PRINT 'Executing: ' + @SQL;
    
    -- TODO: Uncomment for production execution
    -- EXEC sp_executesql @SQL;
    
END
ELSE
BEGIN
    PRINT 'Log backup current: ' + CONVERT(varchar(23), @LogBackupTime, 121);
END;

-- Cleanup and return status
PRINT 'Log backup check completed for ' + @DBName + ' at ' + CONVERT(varchar(23), GETDATE(), 121);

It gives you a reliable, replica-agnostic view of transaction log backup health across your SQL Server environment. Drop it into a scheduled job with your 24-hour threshold, pipe the results to an email alert. Simple, effective monitoring that catches problems before they become outages.