Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

History on the Principle Server

History on the Mirror Server

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:

enter image description here Zoom: Click for full size version

When Ran on the mirror:

enter image description here 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:

enter image description here

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

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
That must have been a fun one to track down. –  Zane Dec 19 '12 at 16:14
add comment

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:

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
share|improve this answer
    
Hi Koz, this was one of the first things i tried i'm afraid, i've just re-tried it now, no change. I'll just bite the bullet and remove the mirroring & re-add it. –  HeavenCore Apr 18 '12 at 9:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.