Sql-server – Using SQL Server Reporting Services for monitoring database performance

I believe problem was that the report definitions no longer matched the stored procedures that were called. After I refreshed all of my reports the problem seems to be resolved.

Unfortunately I can not say for sure that is the final answer as it could be up to 90 days between the last restart and as little as 4 days, so I am still waiting to remove this from my task list.

These are old reports that were originally in version 2005.

You can use Replication Management Objects. You can interrogate them locally or remotely (the better option). The DLLs should be on some systems. If you still have known SQL 2000 instances please test them as it's unclear if modern versions of RMO will still talk down to SQL 2000 instances; 2005 and above should be more reliable.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo")

$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer(".")
if ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
    "Has Replication"
}

There are other flags you can check out. I should tell you that RMO is deprecated (much to my chagrin) but it still works for now.

Another option was to

Import-Module SQLPS -DisableNameChecking
Get-ChildItem SQLSERVER:\SQL\...(server)...\...(instance or DEFAULT)...\Databases | Where { $_.ReplicationOptions -ne "None" }

But SQLPS is part of the 2008 R2 Feature Pack or 2012+. So if you have a server like that you can run it remotely. However also this replication flag can be enabled for other reasons like CDC and so is usually a little misleading but in your case maybe it's just what you're after.