Transactional Replication - how to set up alerts/notification to send alerts before transaction log space is full on a Publisher?

Question

I am setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL recovery mode. Log backup is taken every 15 minutes and full backup is taken daily everyday on all the three server databases used in replication. We want to use the subscriber as a reporting server. Publisher is our main OLTP database.

How can i get alert before my publisher's t-log space is full due to any issue in replication. I do not want my publisher to go down in any case. The t-logs are backed every 15 min on the publisher. Would that mean all the VLFs which are actively read by the log reader can be backed up when log backup is taken? what alerts should I use which will tell me that the log-space is getting full on publisher? Is there any script which can also stop replication/log reader agent when the log space is getting dangerously big on publisher?

I can remove replication and reestablish (with a new snapshot) when the replication is causing issue and building up logs on my publisher. But I dont want to wait for this situation to come (log space is full on publisher) and want to take action before only - like remove replication before only when log space reaches a certain percentage. How can i do that?

Also, what should be my strategy to stop and remove replication? I will be following Microsoft's documentation to remove replication but just not clear if I should stop the Log Reader agent and Distribution agent first before removing replication?

Aleksey Vitsko · Accepted Answer · 2024-06-20T18:01:15.557

How can i get alert before my publisher's t-log space is full due to any issue in replication

what alerts should I use which will tell me that the log-space is getting full on publisher?

You can use below script to check whether log file is more than certain threshold in size, more than 90% full, and send email notification.

create table #LogSpace (
    [Database_Name]             nvarchar(150),
    Log_Size_MB                 decimal(18,2),
    Log_Space_Used_Pct          decimal(5,2),
    [Status]                    int)
-- collect log space information
declare 
    @SQL                        varchar(100),
    @Log_Size_MB                decimal(18,2),
    @Log_Space_Used_Pct         decimal(5,2)
    @Log_Reuse_Wait_Desc        varchar(60)
set @SQL = 'dbcc sqlperf(logspace)'
insert into #LogSpace
exec (@SQL)
-- get log size and fullness for your database in question
select 
    @Log_Size_MB =  Log_Size_MB,
    @Log_Space_Used_Pct = Log_Space_Used_Pct
from #LogSpace
where [Database_Name] = 'YourDatabase'
-- why log is not truncating ?
select 
    @Log_Reuse_Wait_Desc = log_reuse_wait_desc
from sys.databases
where [name] = 'YourDatabase'
-- check if log file grew more than X number of Megabytes and is more than 90% full
if (
    @Log_Size_MB > 10000                        -- specify log size in MB, exceeding which you would start worrying about
    and @Log_Space_Used_Pct > 90                -- more than 90% full
    and @Log_Reuse_Wait_Desc  = 'REPLICATION'   -- this is optional line
    ) begin
-- fire an email alert
exec msdb.dbo.sp_send_dbmail 
        --@profile_name = 'your mail profile name',         -- this is optional, depending on your db mail config
        @recipients = 'your.email@domain.com', 
        @from_address = 'db@domain.com', 
        @reply_to = 'db@domain.com', 
        @subject = 'subject', 
        @body = 'transaction log getting full'

end

You can schedule above script in SQL Agent to be run every, say, 15 minutes to check whether log is getting full, notify you by email, so you can take action.

Is there any script which can also stop replication/log reader agent when the log space is getting dangerously big on publisher?

Refer to another answer - manually remove replication.

score 2 · Answer 2 · answered Jun 20 '24 at 14:19

How can i get alert before my publisher's t-log space is full due to any issue in replication.

It depends on how your transaction log file growth is set up.

If you have pre-grown your transaction log files and capped file size, then you need to monitor the percentage of logfile used and set an alert at a comfortable threshold. You can use DBCC SQLPERF (Transact-SQL) to monitor log space usage for all databases. Another solution is to use Get-DbaDbSpace from dbatools. There are many other solutions available to do the same.

If you allow file growth, you must monitor the free space on the drive (where the log file resides). Again, you can use the dbatools command Get-DbaDiskSpace or other solutions to set up an alert.

Is there any script which can also stop replication/log reader agent when the log space is getting dangerously big on publisher?

Stopping the log reader will not help you in this case. Transaction logs will not be truncated unless the log reader agent processes those records (Sending them to the distribution database). You can remove replication by following Manually remove replication in SQL Server.

Also, what should be my strategy to stop and remove replication? I will be following Microsoft's documentation to remove replication but just not clear if I should stop the Log Reader agent and Distribution agent first before removing replication?

I answered it in the previous section. How can I permanently remove replication by script if I only have access to the subscriber? has some other options.

On a side note, depending on the reporting requirement, have you considered log shipping as a solution?

Transactional Replication - how to set up alerts/notification to send alerts before transaction log space is full on a Publisher?

2 Answers2