/// <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); }