Mirror log empty on primary but fine on the mirror 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!)

Edit: 19/12/2012

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:


Zoom: Click for full size version

When Ran on the mirror:


Zoom: Click for full size version

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???
Well, its took months but i finally found the problem!

The sys.dm_os_performance_counters wasnt being populated at all on the primary server.

Turns out that the counters where not installed, i was able to do this manually as follows:

C:\Windows\system32&gt;lodctr "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSS
QLSERVER\MSSQL\Binn\perf-MSSQLSERVERsqlctr.ini"


Restarted SQL, and boom, the table is now being populated - including my mirroring statistics.