how to find all transactions in a queue

[Gene.]

Hi All
we deal with underpowered replication environment. It seems like replication
stops but RM does not show it.
My theory is that it's moving, but queue is really long.
Is there any way looking at rep. system tables to see coming and going
replication transactions. It would be a proof that rep is working, but queue
is accumulating.

[Paul Ibison]

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

[Gene.]

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.

Post by Paul Ibison
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
select transaction_timestamp
from subscriberdatabasename..MSreplication_subscriptions
Then run this in the distribution database (replace the value with the one
returned from above:)
HTH,
Paul Ibison, www.replicationanswers.com

[Gopal Ashok [MSFT]]

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.

Post by Gene.
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.

Post by Paul Ibison
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
select transaction_timestamp
from subscriberdatabasename..MSreplication_subscriptions
Then run this in the distribution database (replace the value with the one
returned from above:)
HTH,
Paul Ibison, www.replicationanswers.com

[Gene.]

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.

Post by Gopal Ashok [MSFT]
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.

Post by Gene.
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.

Post by Paul Ibison
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
select transaction_timestamp
from subscriberdatabasename..MSreplication_subscriptions
Then run this in the distribution database (replace the value with the one
returned from above:)
HTH,
Paul Ibison, www.replicationanswers.com

[Paul Ibison]

Gene,
if you need to access the numerical data in a query, MSdistribution_status
could be what you're after or as Gopal says
sp_replmonitorsubscriptionpendingcmds. For the actual transactions in
human-readable format, you'll still need sp_browsereplcmds though and the
xact_seq no will need to be provided dynamically.
HTH,
Paul Ibison, www.replicationanswers.com

[Gene.]

Paul

I looked at BOL and it was my first choice to look at this proc / table repl
commands.
However, it shows nothing usefull. I can't even speculate.
Just try to run against your server and you will see the same set again and
again. I can only guess what it is. But it sheds no light on what so ever on
what is inside of the queue or at least what is currently replicating.
I know distributor should have open connection so subscriber. But they
appear so short timed, I did not even see an open connection running anything
on subscriber.
Even though i did, how it would help me to evaluate the size of the queue or
latency?
Try to look at your replication and send me an example of what you see. I
am interested to know how will your relate output from sp_browsereplcmds to
replication queue and how will you find out what distributor applying at this
moment on subscriber.

Post by Paul Ibison
Gene,
if you need to access the numerical data in a query, MSdistribution_status
could be what you're after or as Gopal says
sp_replmonitorsubscriptionpendingcmds. For the actual transactions in
human-readable format, you'll still need sp_browsereplcmds though and the
xact_seq no will need to be provided dynamically.
HTH,
Paul Ibison, www.replicationanswers.com

[Paul Ibison]

Hi Gene,

this goes back to my original email :)

When the distribution agent is running I use DBCC INPUTBUFFER on the
subscriber to see exactly what it is doing, in those cases where I believe
there is a bottleneck.
To see how many commands are in the pipeline I use sp_browsereplcmds.
I have coded a query below for you to use which should be more specific to
your needs as it'll return those commands waiting to go to the subscriber:

declare @xact_seqno varbinary(16)
select @xact_seqno = max(xact_seqno)
from MSsubscriptions
inner join MSpublications
on MSpublications.publication_id = MSsubscriptions.publication_id
inner join MSdistribution_history
on MSdistribution_history.agent_id = MSsubscriptions.agent_id
Where subscriber_db = 'subscriberdb'
AND Publication = 'publisherdbFullNames'
select @xact_seqno

declare @str varchar(255)
exec master..xp_varbintohexstr @xact_seqno, @str out
set @str = left(@str, len(@str) - 8)

select @str

exec sp_browsereplcmds @xact_seqno_start = @str

For a numerical summary, I would use MSdistribution_status.

HTH,

Paul Ibison, www.replicationanswers.com

[Gene.]

Paul, i have different view completely on msrepl table then you.
1. I have 1.5 mil rows in this table and there is no chance that i have so
many rows in a queue. all my queues are current but table is huge.
2. Input buffer will give you idea of what is replicating now. But how does
it help to answer original questions: how big is replication queue and what
is in it?

I beleive an architecture of sql 6.5 server had system tables which hold not
replicated commands. Current architecture does not have it. msrepl_commands
is growing and i am not sure what i should do to stop it.
mergehistory table does not only depend on retention period. If you have
agent up for a long time, system won't trim this table. It depends on 'open
session'. Check cleanup repl history proc. So the only way is to stop sql
agent periodicly to let system trim rows from not active sessions. It's an
respond on your answer on my different post.

Post by Paul Ibison
Hi Gene,
this goes back to my original email :)
When the distribution agent i running I use DBCC INPUTBUFFER on the
subscriber to see exactly what it is doing, in those cases where I believe
there is a bottleneck.
To see how many commands are in the pipeline I use sp_browsereplcmds.
I have coded a query below for you to use which should be more specific to
from MSsubscriptions
inner join MSpublications
on MSpublications.publication_id = MSsubscriptions.publication_id
inner join MSdistribution_history
on MSdistribution_history.agent_id = MSsubscriptions.agent_id
Where subscriber_db = 'subscriberdb'
AND Publication = 'publisherdbFullNames'
For a numerical summary, I would use MSdistribution_status.
HTH,
Paul Ibison, www.replicationanswers.com

[Paul Ibison]

Gene,
I was guessing that the problem is that you are running the agent but the
syncing is ongoing - hence the use of DBCC INPUTBUFFER.
The query method in my first post should help find non-distributed commands
(assuming you are talking about transactional replication?).
HTH,
Paul Ibison

[Gene.]

Paul, I really apprecuate your effort and time for sql you published.
Honestly, I did not have time yet to run it. But I am sure it took you time
to research and develop it.
It is very generous. Thanks again. I will let you know.
by the way I just implemented republishing schema with merge replication:
2 servers - 1st side <--> 2 servers 2nd site.
it was not my choice, opposite. But client insisted on it.
Have you ever done it? Do you see potential issues with it?
I know peer to peer would work more efficent. But i have 25 db's and merge
rep is easear to implement. Multiplying it on each site replication, it was
only the choice.

Post by Paul Ibison
Gene,
I was guessing that the problem is that you are running the agent but the
syncing is ongoing - hence the use of DBCC INPUTBUFFER.
The query method in my first post should help find non-distributed commands
(assuming you are talking about transactional replication?).
HTH,
Paul Ibison