Monthly Archives: March 2013

SQL Server Replication – Troubleshooting – Using verbose history agent profile

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

Advertisements

What is the difference between LooksAlive check and IsAlive check.

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

SQL Server Replication – Find the number of undelivered commands from a Publisher

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;

SQL Server Replication – Find the number of undelivered transactions from a Publisher

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;
%d bloggers like this: