SQLCHAD – Life as a SQL Server DBA

• With transactional replication you have a log reader that reads the log file on the pulisher.  Replication only gives you eyes into the distribution database (assuming the log reader has already put all transactions into the distribution database.  Here is a script you can run on the publisher to see how many transactions are in the publishers log file waiting to be picked up by the Log Reader.  This runs on the publisher (Good candidate to be set up as a reporting services report to be emailed to the DBA’s hourly)

declare @DatabaseName varchar(100)
SET @DatabaseName = '<PublisherDatabaseNameHere>'

SELECT
    instance_name as DatabasName,
    cntr_value as Num_Pending_Trans,
    Case 
        when cntr_value > 10000 
            then 'ALERT' 
        else ' ' 
    End as Status
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Repl. Pending Xacts'
and instance_name IN (@DatabaseName)