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.
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.
SELECT RTRIM(INSTANCE_NAME) AS DBName, RTRIM(COUNTER_NAME) AS CounterName, RTRIM(CNTR_VALUE) AS CounterValue FROM MASTER..SYSPERFINFO WHERE COUNTER_NAME like '%xacts%'
After executing above given TSQL statement, he again provided me the following screen shot.
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
Note: Microsoft Books online is a default reference of all articles .