Microsoft – SQLServer – Database Mirroring – Version 2008/R2 – sys.sp_dbmonitorresults is broken

Microsoft – SQLServer – Database Mirroring – Version 2008/R2 – sys.sp_dbmonitorresults is broken

Tried using “MS SQL Server – SQL Server Management Studio (SSMS)” this morning.

The task was to review one of our Database Mirroring Sessions.

So did the following:

  • Launch SQL Server Management Studio
  • Connected to the “primary” DB Server
  • Selected the database
  • Right click on the “Database” selection
  • From the drop-down menu select “Tasks \ “Launch Database Mirroring Monitor”
  • The “Database Mirroring Monitor” appears, but no joy –  The “Mirroring State” column shows “Error retrieving data”

Profiler Session revealed that the offending code is the call to sys.sp_dbmmonitorresults

sys.sp_dbmonitorresults – Invocation Code

   use msdb
   go

   exec sys.sp_dbmmonitorresults
               @database_name = 
             , @mode = 0
             , @update_table = 1

sys.sp_dbmonitorresults – Original Code

declare @results table
(
      database_name sysname	-- Name of database

    -- 1 = Principal, 2 = Mirror
    , role	  tinyint	-- 1 = Principal, 2 = Mirror

    -- 0 = Suspended, 1 = Disconnected, 2 = Synchronizing
         , 3 = Pending Failover
    -- 4 = Synchronized
    , mirroring_state  tinyint	

    -- 1 = Connected, 2 = Disconnected
    , witness_status       tinyint

    , log_generation_rate   int null	-- in kb / sec
    , unsent_log	    int null -- in kb
    , send_rate	            int null 
    , unrestored_log        int null	-- in kb
    , recovery_rate         int null	-- in kb / sec
    , transaction_delay	    int null -- in ms
    , transactions_per_sec  int null -- in trans / sec
    , average_delay	    int  -- in ms
    , time_recorded	    datetime
    , time_behind	    datetime
    , local_time	    datetime	-- Added for UI		
)

The problem appears to be the use of the int column. One possibly fix will be to use bigint.

sys.sp_dbmonitorresults – Revised Code

declare @results table
(
      database_name sysname	-- Name of database

    -- 1 = Principal, 2 = Mirror
    , role	  tinyint	-- 1 = Principal, 2 = Mirror

    -- 0 = Suspended, 1 = Disconnected, 2 = Synchronizing
       , 3 = Pending Failover
    -- 4 = Synchronized
    , mirroring_state  tinyint	

    -- 1 = Connected, 2 = Disconnected
    , witness_status       tinyint

    , log_generation_rate   bigint null	-- in kb / sec
    , unsent_log	    bigint null -- in kb
    , send_rate	            bigint null 
    , unrestored_log        bigint null	-- in kb
    , recovery_rate         bigint null	-- in kb / sec
    , transaction_delay	    bigint null -- in ms
    , transactions_per_sec  bigint null -- in trans / sec
    , average_delay	    bigint  -- in ms
    , time_recorded	    datetime
    , time_behind	    datetime
    , local_time	    datetime	-- Added for UI		
)

As sys.sp_dbmonitorresults is Vendor Supplied and an alterable System Stored Procedure, it appears that we will have to live with this problem.

If you will like to still access seem functionality, create an identical Stored Procedure, change the int columns to bigint and make sure to name it a bit different than MS Supplied built-in one.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s