Share via

Why is sp_dbmmonitorresults failing with invalid object?

  • Article

I recently setup a simple database mirroring config on my latpop (two instances SQL Server 2005 SP3 etc.).  The databases were configured for HIGH SAFETY but with no witness.  Everything was working fine.  I wanted to simulate a communication failure so I could see the send queue growing.  I just shutdown the mirror instance and then inserted lots of rows into the principal.  Great, so now I executed:

EXEC    sys.sp_dbmmonitorresults @database_name=N'dbm1', 6, 0

But it kept failing with invalid object on dbm_monitor_data.  Looking at sp_dbmmonitorresults I could see that it was failing on line 130.  This was really confusing me so I looked in msdb for the object.  It didn’t exist.

I was suprised to find that the object was only created AFTER launching the database mirroring console.  This creates the necessary monitoring objects in msdb.  Problem solved, after much frustration.

Comments

  • Anonymous
    March 15, 2011
    Yep, hit the same problem here. It seems a strange design decision on the SQL Server team's part to only create this on demand. I've used it for manual monitoring of the mirroring connection across multiple servers, and it becomes tedious to run the mirroring monitor on each server where the stored proc's are required.