Question about snapshot replication and monitoring of msrepl_transactions in the dist.db

OK.
We have this 1 publisher db that is pushing out to two separate
subscribers (one is transactional the other is snapshot). In the
distribution db the publisher_database_id is going to be the same. We
monitor this transaction count by publisher id with the following (
Select count(1) from dbo.MSrepl_transactions where
publisher_database_id = 17). Now that we have added snapshot
replication to one subscriber and transactional to another we have
noticed that this transaction count is not correct. It always says that
there are like 100+ in there waiting, but this is not true. The
transactional publications are always up to date with 0 latency.
It was my understanding that snapshot replication literally just makes a
bcp and push's it over at whatever time you tell it to push. However,
it looks like these extra transactions sitting in my distribution
database have everything to do with the snapshot replication and not the
transactional replication. Does snapshot replication work differently
then i thought? Does snapshot replication actually post transactions to
the distribution db?
Thanks
-comb
what shows up in sp_browsereplcmds. I suspect what you will see there are
the commands which are used for the sync - i.e. to build the tables and
other objects on the subscriber, and the bcp the data there. IIRC the
distribution clean up agent does not clean these up until you are past the
retention period.
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
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1e4953894a6972969896eb@.news.newsreader.co m...
> OK.
> We have this 1 publisher db that is pushing out to two separate
> subscribers (one is transactional the other is snapshot). In the
> distribution db the publisher_database_id is going to be the same. We
> monitor this transaction count by publisher id with the following (
> Select count(1) from dbo.MSrepl_transactions where
> publisher_database_id = 17). Now that we have added snapshot
> replication to one subscriber and transactional to another we have
> noticed that this transaction count is not correct. It always says that
> there are like 100+ in there waiting, but this is not true. The
> transactional publications are always up to date with 0 latency.
> It was my understanding that snapshot replication literally just makes a
> bcp and push's it over at whatever time you tell it to push. However,
> it looks like these extra transactions sitting in my distribution
> database have everything to do with the snapshot replication and not the
> transactional replication. Does snapshot replication work differently
> then i thought? Does snapshot replication actually post transactions to
> the distribution db?
> Thanks
> -comb
|||In article <Oq2WvQxJGHA.3064@.TK2MSFTNGP10.phx.gbl>,
hilary.cotter@.gmail.com says...
> what shows up in sp_browsereplcmds. I suspect what you will see there are
> the commands which are used for the sync - i.e. to build the tables and
> other objects on the subscriber, and the bcp the data there. IIRC the
> distribution clean up agent does not clean these up until you are past the
> retention period.
>
why would the bcp data be in the distribution db? wouldn't it just push
that across the net to the subscriber straight up as a .bcp file? why
would a snapshot need to put anything in the distribution db other then
"hey you need to push this .bcp across".
ok so what you are saying is that snapshot replication DOES actually
post transactions to the distribution db? I thought it just generated
the .bcp, idx and whatever that 3rd file is and pushed those across the
net with just a few instructions on where to put them.?
sp_browsereplcmds times out for me. We have a lot of subscribers on
this box. Is there anyway I can use that sp and just look at a certain
db id?
thanks.