Why is sp_dbmmonitorresults failing with invalid object?
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.
