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.

Question about snapshot article defaults and indexes

I have recently been comparing the size of the source and replicated
tables and realized that indexes are not being replicated. The GLOBAL
(Applies to all) article defaults for the snapshot are set as follows:
Copy objects to destination
Indexes for primary keys are always copied
Include declared referential integrityCHECKED
Clustered indexesCHECKED
Nonclustered indexesCHECKED
User triggersNOT CHECKED
Extended propertiesNOT CHECKED
CollationNOT CHECKED
I these defaults should transfer the indexes correctly so I had to look
for another culprit. We have a script which creates three tables daily
so I decided to look at the article defaults for those tables and found
that only the 'Include declared referential integrity box is CHECKED.
QUESTION: What is the stored procedure where these variables can be
set? Is it sp_addarticle and if so what fields?
If we need to use the replicated db what would have been the
implication of not having the proper indexing. Performance?
to set them use the schemaoption parameter of sp_addarticle.
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