/// <summary>
/// Get replication details (command delivered/undelivered) by publication, subscriber
/// Must be executed on distribution db
/// </summary>
/// <param name="d">Your smo database (distribution)</param>
/// <returns>a datatable</returns>
public static DataTable GetReplicationDetails(this smo.Database d)
{
DataTable dt = new DataTable();
if (d.IsDistributor())
{
string sql = @"SELECT SUM(ds.UndelivCmdsInDistDB) AS [Undelivered]
, SUM(ds.DelivCmdsInDistDB) AS [Delivered]
, ss.name AS [Subscriber]
, sp.name AS [Publisher]
, da.publication AS [Publication]
, da.publisher_db AS [Database]
, da.name AS [Agent]
FROM dbo.MSdistribution_agents da WITH (READUNCOMMITTED)
INNER JOIN sys.servers sp WITH (READUNCOMMITTED)
ON sp.server_id = da.publisher_id
INNER JOIN sys.servers ss WITH (READUNCOMMITTED)
ON ss.server_id = da.subscriber_id
INNER JOIN dbo.MSdistribution_status ds WITH (READUNCOMMITTED)
ON ds.agent_id = da.id
GROUP BY
sp.name
, ss.name
, da.name
, da.id
, da.publication
, da.publisher_db
ORDER BY undelivered DESC
, delivered DESC";
dt = d.ExecuteWithResults(sql).Tables[0];
}
return(dt);
}
/// <summary>
/// Get commands count by article
/// Must be executed on distribution db
/// </summary>
/// <param name="d">Your smo database (distribution)</param>
/// <returns>a datatable</returns>
public static DataTable GetReplicationCommandsByArticle(this smo.Database d, string subscriber, string database)
{
DataTable dt = new DataTable();
if (d.IsDistributor())
{
string sql = string.Format(@"SELECT s.name AS [Subscriber]
, sub.publisher_db AS [Database]
, a.article AS [Article]
, COUNT(*) AS [Commands Count]
FROM dbo.MSrepl_commands c (NOLOCK)
INNER JOIN dbo.MSsubscriptions sub (NOLOCK) ON c.publisher_database_id = sub.publisher_database_id
AND c.article_id = sub.article_id
INNER JOIN dbo.MSarticles a (NOLOCK) ON sub.publisher_id = a.publisher_id
AND sub.publication_id = a.publication_id
AND sub.article_id = a.article_id
INNER JOIN sys.servers s ON sub.subscriber_id = s.server_id
WHERE s.name = '{0}'
AND sub.publisher_db = '{1}'
GROUP BY s.name
, sub.publisher_db
, a.article
ORDER BY [Commands Count] DESC
, [Subscriber]", subscriber, database);
dt = d.ExecuteWithResults(sql).Tables[0];
}
return(dt);
}
/// <summary>
/// Get transactions opened for each replicated databases
/// Must be executed on distribution db
/// </summary>
/// <param name="d">Your smo database (distribution)</param>
/// <returns>a datatable</returns>
public static DataTable GetReplicationTransactionsByDatabase(this smo.Database d)
{
DataTable dt = new DataTable();
if (d.IsDistributor())
{
string sql = @"SELECT d.publisher_db AS [Database]
, COUNT(*) AS [Transactions Count]
FROM MSrepl_transactions t
INNER JOIN MSpublisher_databases d ON t.publisher_database_id = d.id
GROUP BY d.publisher_db
ORDER BY [Transactions Count] DESC";
dt = d.ExecuteWithResults(sql).Tables[0];
}
return(dt);
}
/// <summary>
/// get error from distribution
/// </summary>
/// <param name="d">your smo distribution database</param>
/// <param name="startDate"></param>
/// <returns>a datatable</returns>
public static DataTable GetReplicationErrorLog(this smo.Database d, DateTime startDate)
{
DataTable dt = new DataTable();
if (d.IsDistributor())
{
string sql = string.Format(@"IF EXISTS(SELECT * FROM sys.objects WHERE name = 'MSrepl_errors')
BEGIN
SELECT TOP 10000 DB_NAME() [Database]
, [time]
, source_name AS [Source Name]
, error_text AS [Error Text]
FROM dbo.MSrepl_errors WITH (READUNCOMMITTED)
WHERE [time] >= '{0}'
order by time desc
END", startDate.ToString("yyyyMMdd hh:mm:ss"));
dt = d.ExecuteWithResults(sql).Tables[0];
}
return(dt);
}
/// <summary>
/// Get average latency/rate history from distribution database, group by 10 minutes
/// </summary>
/// <param name="d">Your smo database (distribution)</param>
/// <param name="startDate">Start DateTime</param>
/// <param name="endDate">End DateTime</param>
/// <returns>a datatable</returns>
public static DataTable GetReplicationLatencyStats(this smo.Database d, DateTime startDate, DateTime endDate)
{
DataTable dt = new DataTable();
if (d.IsDistributor())
{
string sql = string.Format(@"IF EXISTS(SELECT * FROM sys.objects WHERE name = 'MSdistribution_history')
BEGIN
SELECT CONVERT(CHAR(15), time, 121) + '0:00' dt
, ROUND(AVG(CAST(current_delivery_latency AS bigint)), 0) AS [Average Delivery Latency]
, ROUND(AVG(CAST(current_delivery_rate AS bigint)), 0) [Average Delivery Rate]
, db_name() as [Database]
FROM dbo.MSdistribution_history WITH (READUNCOMMITTED)
WHERE current_delivery_latency > 0
AND time BETWEEN '{0}' AND '{1}'
GROUP BY CONVERT(CHAR(15), time, 121) + '0:00'
ORDER BY dt DESC
END", startDate.ToString("yyyyMMdd hh:mm:ss"), endDate.ToString("yyyyMMdd hh:mm:ss"));
dt = d.ExecuteWithResults(sql).Tables[0];
}
return(dt);
}