We have dozens of principle servers and corresponding mirrors, most are working fine, however, 1 of them is acting strange. Specifically, the
sp_dbmmonitorresults proc returns all columns as NULL for a given principle database whereas
sp_dbmmonitorresults on the mirror returns valid information:
Now, i have a rough idea what the cause was; when the principle and mirror servers where setup, the regional settings where incorrect on the primary (the time was correct but was using a +4 region (America etc), the time region was corrected to GMT +0 AFTER the mirroring was setup (school boy error i know, but c'est la vie!)
Today i removed the mirroring, deleted the copies on the mirror and re-setup the mirroring, however this did not solve the problem! I'm going to pop a bounty on this question to try and get to the bottom of this.
To confirm, if i run
EXEC sp_dbmmonitorresults @database_name = 'ProScript' manually on each server it returns NULL for most values on the primary but returns fine on the mirror, as shown:
When Ran on primary:
When Ran on the mirror:
As you can see, the times are current and the database is synchronised but the log_generation_rate, unsent_log, send_rate etc vare all NULL on the primary?
Both servers have the montior job running every minute with
exec sys.sp_dbmmonitorupdate as a job step etc.
Looking at the source of sys.sp_dbmmonitorupdate it grabs these values from
sys.dm_os_performance_counters - so if i run the following on the mirror:
SELECT counter_name , cntr_value FROM sys.dm_os_performance_counters WHERE instance_name = 'ProScript' AND counter_name IN ( N'Log Send Queue KB', N'Log Bytes Sent/sec', N'Redo Queue KB', N'Redo Bytes/sec', N'Transaction Delay', N'Log Bytes Flushed/sec', N'Transactions/sec' )
I get good results:
However, if i run the same SQL on the primary there are no rows!
This would suggest that the
sys.dm_os_performance_counters table isnt being populated for the mirroring? What would cause that???