Hello Geeks and welcome to the Day 54 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
Till yesterday we have covered most of the troubleshooting related DMVs. They include, database, OS, memory and execution related DMVs. Today I will be talking about very less used or known DMVs which are useful in particular scenarios. Let us start with replication today. I will be covering sys.dm_repl_articles.
Sys.dm_repl_articles provides the list of all articles published in a database which are in the articles cache. You will not see an entry unless there are transactions on that article. The important columns in this DMV include the destination object details. wszXXXX gives the destination object name, replication procedures, etc. The artgenXXXX columns without a number 2 in them gives the replication procedures template. If there is a number 2 included it is used for reconciling during concurrent snapshots.
Sys.dm_repl_articles is very useful when troubleshooting replication scenarios. You can use it for checking the replication procedures schema, the destination object schema and name. You can check from fPubAllowUpdate if the publication allows updating subscriptions. intPublicationOptions will let you know if it is peer-to-peer, local changes only or enabled for non-SQL subscribers.
SELECT * FROM sys.dm_repl_articles WHERE artpubid = 15
So now you know how to check articles details using sys.dm_repl_articles. Tomorrow I will cover another DMV. Stay tuned. Till then