Mirror log empty on primary but fine on the mirror

Question

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???

Answer 1

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>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.

Answer 2

Have you tried removing the database mirroring monitoring job on the principal instance and recreating it?

This doesn't involve re-initializing the database mirroring session, just dropping and recreating the job using the same stored procedures used by SSMS to manage the database mirroring monitor.

Here are the links to the Books Online entries:

• sp_dbmmonitordropmonitoring
• sp_dbmmonitoraddmonitoring

And a short sample script:

    -- you'll need to be connected to the instance as a member
    -- of the sysadmin fixed server role, of course.

    -- delete the database mirroring monitoring job

    exec msdb.dbo.sp_dbmmonitordropmonitoring;
    go

    -- rebuild the database mirroring monitoring job
    -- the procedure accepts an optional parameter
    -- specifying the number of minutes between updates.
    -- the default is 1 minute.

    exec msdb.dbo.sp_dbmmonitoraddmonitoring;
    go