Re: how to find all transactions in a queue

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Gopal, Paul

did you guys try to run this command yourself? It always produces the same
output which i can not immagine would be a list of not distributed
transactions in a queue.
I am familiar with an architecture of replication for many years already on
Sybase and sql server.
What i think is that one has to be able to browse queue generaly and see the
size of the queue, being able to list transactions in a queue and see how it
is moving.

I am not sure what you refer to when you say you say that something is
exposed in replication monitor. There is nothing in replication monitor. All
you can do is insert trace. In case of high latency it does not help. One
still does not know how big it is and whether it's moving. I agree with you
about counters - this is the only what's left but it is a metric. If I wanted
to present it to my users as a proof, they may not be convinced since the
most intuitive thing would be the list of not replicated transactions in sql
terms. I will try the command you gave me.
Hopefully, you understand my task: i want to see:
1. list of not distributed yet commands in a queue.
2. Hoping that this list is dynamic, i would be able to see how many
transactions coming in and out.

Thanks anyway.


"Gopal Ashok [MSFT]" wrote:

Like paul mentioned, Msrepl_commands is the queue of transactions read from
the log that is then delivered to the subscriber by the distribution agent.
Im assuming you are on SQL Server 2005. You should first determine whether
the latency is infact due to a slow distribution agent or the log reader
agent using tracer token. You can run sp_replmonitorsubscriptionpendingcmds
to determine how many pending commands are there to be replicated for a
particular subscriber. BTW this is exposed through replication monitor.

I'm not sure what you are trying to determine here, but what you need to
understand is the throughput of the agents in cmds\sec. That along with the
length of the queue will give you the approximate catch up time, which again
is displayed in the 2005 monitor.

slow down of distribution agent is typically due to the fact that the
subscriber side is not able to apply changes fast enough.


"Gene." <Gene@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7776883A-1777-4D84-915E-5F7A4F7DD4A1@xxxxxxxxxxxxxxxx
Paul, i looked through MSrepl_commands table. That's what i beleive this
proc is based on. I am not sure what it shows, may be command which is
used
by rep agent.
It's not what i am looking for.
I am looking for queue which is definately exists in a replication. May be
internaly. I am looking to relate latency to this queue.

Now, it could not understand your reference to dbcc inputbuffer. What it
has
to do with replication? Distribution process comes and goes. Does not stay
as
open process with changing content. And i do not see how it could help to
the
task of evaluation of not replicated yet transactions.
Sorry.

"Paul Ibison" wrote:

For normal transactional you can use sp_browsereplcmds and in the case of
a
queued subscriber use sp_replqueuemonitor . Then you can use DBCC
INPUTBUFFER
on the subscriber to see where abouts you are up to.

Alternatively, use this to get the timestamp of the latest command to be
replicated:
select transaction_timestamp
from subscriberdatabasename..MSreplication_subscriptions

Then run this in the distribution database (replace the value with the
one
returned from above:)
sp_browsereplcmds @xact_seqno_start = '0x000000AF00000043000B00000001'

HTH,
Paul Ibison, www.replicationanswers.com

.

Relevant Pages

  • Re: dcdiag - advertising errors on newley promoted domain controller
    ... Default Server: uksccmads01.jw-uk.jameswalker.co.uk ... Output from dnslint on uksccmads01: No erros found, ... Active Directory Forest Replication GUIDs Found: ... From a command prompt try and see if you get any additional info ...
    (microsoft.public.windows.server.active_directory)
  • RE: MSmerge_history - Production Issue
    ... You should also be able to delete the merge history beyond the retention ... running continuous Merge replication each with 1 updating subscriber. ... Distribution DB properties are set as follows: ...
    (microsoft.public.sqlserver.replication)
  • Re: Replication Issues
    ... Looking for a SQL Server replication book? ... The above command will indicate which table on the subscriber is ... Please also check that the distribution agent is ether running ... Last transaction timestamp: 0x0000042d000003ff000100000000 ...
    (microsoft.public.sqlserver.replication)
  • Re: Help with replication error
    ... The row was not found at the Subscriber when applying the replicated command. ... I have a transactional replication with row filters with UDFs on some tables ... that is no Ionger in the database that you are relicating to. ...
    (microsoft.public.sqlserver.replication)
  • Re: replicated commands not executed on the subscriber side?
    ... Unlike the replication procedure when changes made at Subscriber, ... Distribution Agent. ... In our case, when running following the distributed transaction, the ...
    (microsoft.public.sqlserver.replication)