sys.dm_repl_articles – Day 54 – One DMV a Day

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.

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

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook


About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.