Read this very good article authored by Chris Skorlinski and hence sharing the same in my blog.
The article talks about the efficient use of “-HistoryVerboseLevel 2“. This allows additional diagnostic information while we troubleshoot replication in SQL Server.
Please find the link to the article: HistoryVerboseLevel
LooksAlive Check:
This check performs a basic verification that the SQL Server service is running on the hosted node which should be online on a given interval of time. The default time that has been set is 5 seconds.
In case the check fails, the cluster service performs another check which is very throrugh in nature called IsAlive Check to verify the failure.
The LooksAlive check is also known as Basic resource health check in Windows Server 2008.
IsAlive check:
This process checks and verifies the cached result of the internal IsAlive process in the SQL Server resource DLL. The internal IsAlive process runs every 60 seconds and verifies if SQL server is online on not. The check uses SELECTÂ @@SERVERNAME to verify the state of the SQL Server.
In case the query fails , it runs additional retry login to avoid failures. On the event of retry logic failure, the internal IsAlive process shuts down the SQL server service and failover event is triggered. The IsAlive check also known as Thorough resource health check in Windows Server 2008
The below mentioned script helps us to find the number of Undelivered Commands from the publisher.
Courtesy: http://www.sqlsoldier.com/wp/sqlserver/countingunreplicatedcommandsandtransactions
With MaxXact (ServerName, PublisherDBID, XactSeqNo) As (Select S.name, DA.publisher_database_id, max(H.xact_seqno) From distribution.dbo.MSdistribution_history H with(nolock) Inner Join distribution.dbo.MSdistribution_agents DA with(nolock) On DA.id = H.agent_id Inner Join master.sys.servers S with(nolock) On S.server_id = DA.subscriber_id Group By S.name, DA.publisher_database_id) Select MX.ServerName, MX.PublisherDBID, COUNT(*) As CommandsNotReplicated From distribution.dbo.MSrepl_commands C with(nolock) Right Join MaxXact MX On MX.XactSeqNo < C.xact_seqno And MX.PublisherDBID = C.publisher_database_id Group By MX.ServerName, MX.PublisherDBID;
The below mentioned script helps us to find the number of undelivered transactions from the publisher.
Courtesy: http://www.sqlsoldier.com/wp/sqlserver/countingunreplicatedcommandsandtransactions
With MaxXact (ServerName, PublisherDBID, XactSeqNo) As (Select S.name, DA.publisher_database_id, max(H.xact_seqno) From distribution.dbo.MSdistribution_history H with(nolock) Inner Join distribution.dbo.MSdistribution_agents DA with(nolock) On DA.id = H.agent_id Inner Join master.sys.servers S with(nolock) On S.server_id = DA.subscriber_id Group By S.name, DA.publisher_database_id) Select MX.ServerName, MX.PublisherDBID, COUNT(*) As TransactionsNotReplicated From distribution.dbo.msrepl_transactions T with(nolock) Right Join MaxXact MX On MX.XactSeqNo < T.xact_seqno And MX.PublisherDBID = T.publisher_database_id Group By MX.ServerName, MX.PublisherDBID;
Applied SQL... 