--Execute on Distribution Database:
--Get all Distribution Agents and UndelivCmdsInDistDB:
Select
@@SERVERNAME as 'Distribution Server', DB_Name() as 'Distribution Database', dist_ag.Name as 'Distribution Agent', dist_ag.Creation_Date, p.Profile_Name,
dist_st.UndelivCmdsInDistDB, dist_st.DelivCmdsInDistDB, a.Article, s.Data_Source as 'Subscriber', dist_ag.Publisher_DB, dist_ag.Publication,
dist_ag.Subscriber_DB, dist_ag.Subscriber_Login,
CASE dist_ag.subscriber_security_mode
WHEN '0' Then 'Microsoft SQL Server Authentication'
WHEN '1' Then 'Microsoft Windows Authentication'
End As 'Subscriber_Security_Mode',
CASE dist_ag.subscription_type
When '0' Then 'Push'
When '1' Then 'Pull'
When '2' Then 'Anonymous'
End As 'Subscription Type'
from Distribution.dbo.MSdistribution_agents dist_ag
Inner Join Distribution.dbo.MSdistribution_status dist_st on dist_ag.id = dist_st.agent_id
Inner Join Distribution.DBO.MSARTICLES a on a.article_id = dist_st.article_id
Inner Join SYS.Servers s on s.server_id = dist_ag.subscriber_id
Inner Join msdb.dbo.MSagent_profiles p on p.profile_id = dist_ag.profile_id
--Note: This query joins on MSdistribution_status which is a view that looks on MSRepl_Commands table. MSRepl_Commands is the queue in the Distribution Database and the record count here changes when the Distribution Cleanup SQL Job runs and when the Log Reader Agent writes commands to it.
|
|
|
|
|
|