Undistributed Commands telling fibs

• quackhandle1975

  SSChampion

  Points: 11055

  More actions

  March 26, 2015 at 6:00 am

  #296338

  I have transactional replication from two separate publishers going to same subscription instance (separate subs dbs).

  Both are working fine, but one is showing nearly 500k undistributed commands, but I suspect this is wrong. I have been reading this article, and where the author has taken a subset of sys.sp_replmonitorsubscriptionpendingcmds, however I can't get this code to run, I get error: Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.

  Any suggestions?

  declare @avg_rate int

  declare @retcode int

  DECLARE @mstestcountab TABLE ( pendingcmdcount int )

  select @avg_rate = isnull(avg(delivery_rate),0.0)

  from dbo.MSdistribution_history

  where agent_id = 3

  --insert into @mstestcountab (pendingcmdcount)

  exec @retcode = sys.sp_MSget_repl_commands

  @agent_id = 3

  ,@last_xact_seqno = xact_seqno

  ,@get_count = 2

  ,@compatibility_level = 9000000

  select pendingcmdcount from @mstestcountab

  select

  pendingcmdcount

  ,N'estimatedprocesstime' = case when (@avg_rate != 0.0)

  then CAST((cast(pendingcmdcount as float) / @avg_rate) as int)

  else pendingcmdcount end

  from @mstestcountab

  qh

  [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

• quackhandle1975

  SSChampion

  Points: 11055

  More actions

  March 26, 2015 at 7:05 am

  #1786540

  I got this resolved from a comment on Kendra Little's blog post on Monitoring Replication[/url]

  See comment from Heather Sullivan, the original article does not really say that you have to add in the xact_seqno and delivery rate, so you need these parameters in the first instance. Then add them into the main script.

  Thank you Heather!

  qh

  [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]