Reqplication queues up without any errors

I have two publications (with only one article in each) based on the same
source table. Both of these publications have one pull subscription each.
Using sp_browsereplcmds and querying MSrepl_commands, I noticed that
replication queues up for these two articles. How can I check whether this
data has been replicated -- (I do not believe it has).
Also, I am not seeing any errors in replication but the queue keeps growing.
Why are no errors being thrown out?
select * from distribution.dbo.MSdistribution_status and check the number of
undelivered commands.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>I have two publications (with only one article in each) based on the same
> source table. Both of these publications have one pull subscription each.
> Using sp_browsereplcmds and querying MSrepl_commands, I noticed that
> replication queues up for these two articles. How can I check whether
> this
> data has been replicated -- (I do not believe it has).
> Also, I am not seeing any errors in replication but the queue keeps
> growing.
> Why are no errors being thrown out?
>
|||The number of rows in MSrepl_commands and MSrepl_transactions will grow
without limitation. They are not removed when they have been applied to
each subscriber. Instead, a clean up process runs at a periodic interval.
This cleanup process removes anything older than the retention interval that
you specified. So, if you have a retention interval of 72 hours, the
cleanup process will remove anything from MSrepl_commands and
MSrepl_transactions that have been successfully applied to all subscribers
needing that transaction which are more than 72 hours old. Just because
data exists in these two tables doesn't mean that it has not been sent to
the subscriber.
MSdistribution_status will tell you how many rows have not been sent. You
can also spot check this using sp_browsereplcmds. It will give you the
exact data for the change sent. You can then simply run a select statement
against the subscriber to verify that the change made it.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>I have two publications (with only one article in each) based on the same
> source table. Both of these publications have one pull subscription each.
> Using sp_browsereplcmds and querying MSrepl_commands, I noticed that
> replication queues up for these two articles. How can I check whether
> this
> data has been replicated -- (I do not believe it has).
> Also, I am not seeing any errors in replication but the queue keeps
> growing.
> Why are no errors being thrown out?
>
|||Thank you Mike and Hilary. Your answers were very helpful.
"Michael Hotek" wrote:

> The number of rows in MSrepl_commands and MSrepl_transactions will grow
> without limitation. They are not removed when they have been applied to
> each subscriber. Instead, a clean up process runs at a periodic interval.
> This cleanup process removes anything older than the retention interval that
> you specified. So, if you have a retention interval of 72 hours, the
> cleanup process will remove anything from MSrepl_commands and
> MSrepl_transactions that have been successfully applied to all subscribers
> needing that transaction which are more than 72 hours old. Just because
> data exists in these two tables doesn't mean that it has not been sent to
> the subscriber.
> MSdistribution_status will tell you how many rows have not been sent. You
> can also spot check this using sp_browsereplcmds. It will give you the
> exact data for the change sent. You can then simply run a select statement
> against the subscriber to verify that the change made it.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
> news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>
>
|||Actually, one more question. How do I check which replication commands /
transactions are not yet delivered? MSdistribution_Status ?
"Michael Hotek" wrote:

> The number of rows in MSrepl_commands and MSrepl_transactions will grow
> without limitation. They are not removed when they have been applied to
> each subscriber. Instead, a clean up process runs at a periodic interval.
> This cleanup process removes anything older than the retention interval that
> you specified. So, if you have a retention interval of 72 hours, the
> cleanup process will remove anything from MSrepl_commands and
> MSrepl_transactions that have been successfully applied to all subscribers
> needing that transaction which are more than 72 hours old. Just because
> data exists in these two tables doesn't mean that it has not been sent to
> the subscriber.
> MSdistribution_status will tell you how many rows have not been sent. You
> can also spot check this using sp_browsereplcmds. It will give you the
> exact data for the change sent. You can then simply run a select statement
> against the subscriber to verify that the change made it.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
> news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>
>
|||Check the subscriber and look at the value of transaction_timestamp for
your publication
select transaction_timestamp From MSreplication_subscriptions
Compare this with the xact_Seqno or xact_id in msrepl_transactions in your
publisher's distribution database. Get the xact_seqno - note that this
number will not be an exact match but rather in the range.
The number of rows remaining for that publication are the number of commands
which have to be applied there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
news:E478A229-D8A4-4511-807B-B977E8DA5E95@.microsoft.com...[vbcol=seagreen]
> Actually, one more question. How do I check which replication commands /
> transactions are not yet delivered? MSdistribution_Status ?
> "Michael Hotek" wrote: