Repl. Pending Xacts performance counter and high value in SQL Server

Repl. Pending Xacts performance counter and high value in SQL Server:

 

After publishing Performance Counter article yesterady, one of my regular blog reader come up with very interesting question. He don’t have replication enabled in his production server yet “Repl. Pending Xacts” performance counter was giving very high value (4543857846) to him. Since this is in billion, I have asked him to provide me with the result of Performance Counter  view I had in my article so he provided me with the following screen shot.

 

Note: If you have directly encountered this article, I would like to request you to please visit Performance Counter in SQL Server article to get the script which has raised this post. click here to visit that article.

Performance Counter Result

After confirming that the value he said is really there, I asked him to provide me further information about his environment by executing following query so that I can see which databases are causing this issue.

[sourcecode language=”sql”]SELECT
RTRIM(INSTANCE_NAME) AS DBName,
RTRIM(COUNTER_NAME) AS CounterName,
RTRIM(CNTR_VALUE) AS CounterValue
FROM MASTER..SYSPERFINFO
WHERE COUNTER_NAME like ‘%xacts%'[/sourcecode]

After executing above given TSQL statement, he again provided me the following screen shot.

Repl. Pending Xacts bug

If you observe above given screen shot, you come to know that this problem is being initiated by MODEL and MASTER database. If I would have found any user databases, I would have investigated more to find whether replication is enabled intentionally or unintentionally but these are system database and can’t be replicated at all. This is bug of performance counter so you don’t need to worry about this one.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Repl. Pending Xacts performance counter and high value in SQL Server”

Comments are closed.