poor performance of publication

sql2005 standard edition
In replication monitor, under Publication tab, the Current Average
Performance and Current Worst Performance show "Poor" for one publication,
and this has been like this for a few days.
The publisher server is not under heavy load at all. In Perfmon I checked
Delivery Latency and Delivered Trans.sec for the related replication jobs and
all show 0.
Could anybody tell me what else I need to look into and where to start with
troubleshooting? I am new to replication. Thanks a lot.
Claudia
Look on the subscriber to see if there are user triggers on this or
different indexes. Also is the subscriber the same hardware and
configuration as the other subscribers? Are there any other processes
running on this machine?
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
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:1292940D-2268-4C27-ADED-5206FC3CF225@.microsoft.com...
> sql2005 standard edition
> In replication monitor, under Publication tab, the Current Average
> Performance and Current Worst Performance show "Poor" for one publication,
> and this has been like this for a few days.
> The publisher server is not under heavy load at all. In Perfmon I checked
> Delivery Latency and Delivered Trans.sec for the related replication jobs
> and
> all show 0.
> Could anybody tell me what else I need to look into and where to start
> with
> troubleshooting? I am new to replication. Thanks a lot.
> Claudia
>
|||The subscriber hardware is the same. No triggers on subscriber. The tables
involved in replication are all small tables (18 tables, the biggest one has
600 records, and the rest of tables have < 100 records). There're a few
connections to the subscriber DB but they are in sleeping mode. The only
connections with Open Transaction showing 1 are from the replication, which
seems normal.
The articles
"Hilary Cotter" wrote:

> Look on the subscriber to see if there are user triggers on this or
> different indexes. Also is the subscriber the same hardware and
> configuration as the other subscribers? Are there any other processes
> running on this machine?
> --
> 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
>
> "Claudia" <Claudia@.discussions.microsoft.com> wrote in message
> news:1292940D-2268-4C27-ADED-5206FC3CF225@.microsoft.com...
>
>
|||Have you updated the indexes?
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
"Claudia" <Claudia@.discussions.microsoft.com> wrote in message
news:7D92F872-3FFE-4AF6-B367-D58CD60C85E2@.microsoft.com...[vbcol=seagreen]
> The subscriber hardware is the same. No triggers on subscriber. The tables
> involved in replication are all small tables (18 tables, the biggest one
> has
> 600 records, and the rest of tables have < 100 records). There're a few
> connections to the subscriber DB but they are in sleeping mode. The only
> connections with Open Transaction showing 1 are from the replication,
> which
> seems normal.
>
> The articles
> "Hilary Cotter" wrote:

PollingInterval and HistoryVerboseLevel

I have read, and it has been recommended here, that in order to improve
transactional replication performance I should set the PollingInterval and
HistoryVerboseLevel to 0 on both the Log Reader and Distribution Agent jobs.
As we are utilizing scripts to enable and configure replication, I was
wondering if there were any built-in stored procedures that might help me do
this. I was able to use the @.optional_command_line switch on
sp_addsubscription to get these parameters set for the Distribution Agent.
Is there anything similar for the Log Reader Agent? I’d really prefer not to
have the users modify the Log Reader Agent job.
Thanks,
Mark
Mark,
I'd probably investigate sp_update_agent_profile for this. As far as i know
it cant be done pre-emptively.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Don't set the historyverboselevel to 0 for the log reader. Use profiles to
package up your agent properties and then modify the job to use the
different profile. Also have a look at -DefinitionFile to bundle up addition
properties not present in the profile.
You can change the profile used by using sp_update_agent_profile. You can
change the command line arguments by using sp_update_jobstep
Here is an example:
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
SELECT @.JobID = 0x644DDB4D684E324080CB1C265740831E
EXECUTE @.ReturnCode = msdb.dbo.sp_update_jobstep @.job_id = @.JobID,
@.step_id = 2, @.command = N'-Subscriber [DEV-HCOTTER\SQL2000] -SubscriberDB
[pubs] -Publisher [DEV-HCOTTER\SQL2000] -Distributor
[DEV-HCOTTER\SQL2000] -DistributorSecurityMode 1 -PublisherDB
Northwind] -Continuous -OutputverboseLevel 3', @.database_name = N''
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"mrprice" <mrprice@.discussions.microsoft.com> wrote in message
news:14890C83-64E9-4C97-8418-49D5E4405B46@.microsoft.com...
>I have read, and it has been recommended here, that in order to improve
> transactional replication performance I should set the PollingInterval and
> HistoryVerboseLevel to 0 on both the Log Reader and Distribution Agent
> jobs.
> As we are utilizing scripts to enable and configure replication, I was
> wondering if there were any built-in stored procedures that might help me
> do
> this. I was able to use the @.optional_command_line switch on
> sp_addsubscription to get these parameters set for the Distribution Agent.
> Is there anything similar for the Log Reader Agent? I'd really prefer not
> to
> have the users modify the Log Reader Agent job.
> Thanks,
> Mark

Pls Help-2000 Replication

How do I know whether the record in a row was insert,update and delete that replicated to a subscriber?
Is there a flag or an indicator in msmerge_contents,msmerge_tombstone ,msmerge_genhistory tables or any other msdb/replication system tables?You would have to catch the commands before they got deleted out of Distribution database with the system stored procedure called sp_browsereplcmds. Or you can stop the distribution clean up job temporarily to track it down. The system stored procedure can only be executed in the Distribution database. You have to look for the Commands column and see what execute the command, sp_MSDel_, sp_MSIns_, or sp_MSUpd_.|||Hi Joej,

Is there a table that specifically store the flag a changes occured in Subscriber or Publisher database? If I stop the distribution clean up job, which tables can I refer to?
Thanks

Originally posted by joejcheng
You would have to catch the commands before they got deleted out of Distribution database with the system stored procedure called sp_browsereplcmds. Or you can stop the distribution clean up job temporarily to track it down. The system stored procedure can only be executed in the Distribution database. You have to look for the Commands column and see what execute the command, sp_MSDel_, sp_MSIns_, or sp_MSUpd_.|||Sory I left out a sentence. Let say, just to know whether the records in MSMERGE_CONTENTS and MSMERGE_TOMBSTONE has been send over to replicate on Publisher or Subscriber.

Thanks again!

Originally posted by eshl
Hi Joej,

Is there a table that specifically store the flag a changes occured in Subscriber or Publisher database? If I stop the distribution clean up job, which tables can I refer to?
Thanks|||msrepl_commnads and msrepl_transactions.|||I just tried to insert,update and delete, and I stop the Agent that clean the distribution database. I couldnt find any record in msrepl_commands and msrepl_transactions.

Originally posted by joejcheng
msrepl_commnads and msrepl_transactions.|||back to this issue, I could find the history record that stored the latest record that was merged in th publisher and subscriber. By using this, I will be able to know which record was generated and which wasnt. This is helpful in determining the records that was replicated or not yet.
I m looking into modifying the replication engine,MSSQL replication engine is flexible.

Originally posted by eshl
I just tried to insert,update and delete, and I stop the Agent that clean the distribution database. I couldnt find any record in msrepl_commands and msrepl_transactions.

Please suggest type Of Replication suitable in this scenario !

Hi All:
I have 2 sites, one here in India & one at Montreal working on same
database, hosted here (India), using ASP Application.
The link is dedicated over WAN. Now I want both sites to access their own DB
at their local ends (structure being same) & then replicate/snycronize both
Databases periodically, say every 60 mins or in worst case once every day.
Both sites receive customer inbound calls & people take orders, troubleshoot
product. The DB size is 3 GB & growing, though slowly.
What is best type of replication which i can use here?
Please suggest!
Thanks,
Anukul
anukuls@.yahoo.com
If your schema is static, and your users will never be accessing these
databases at the same time or if you can minimize conflicts using
partitioning you could use bi-directional transactional replication.
Otherwise you should look at merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <Anukul@.discussions.microsoft.com> wrote in message
news:10746DEA-A21D-4702-A44B-B46631DC89FC@.microsoft.com...
> Hi All:
> I have 2 sites, one here in India & one at Montreal working on same
> database, hosted here (India), using ASP Application.
> The link is dedicated over WAN. Now I want both sites to access their own
DB
> at their local ends (structure being same) & then replicate/snycronize
both
> Databases periodically, say every 60 mins or in worst case once every day.
> Both sites receive customer inbound calls & people take orders,
troubleshoot
> product. The DB size is 3 GB & growing, though slowly.
> What is best type of replication which i can use here?
> Please suggest!
> Thanks,
> Anukul
> anukuls@.yahoo.com
|||Thanks Hilary!
Yes the Database structure is static, but may change once every 1 month.
Also, users will be accessing the DB simultaneously at both ends, as Inbound
customer calls will be received at both sites, so changes relating to a
customer's address, name, order may get updated. I am over looking the
scenario where same customer calls at same time at both sites, but the same
customer's data may be worked on at both sites at same time. My ASP
application will change as it uses identities right now, but will I will
create site specific ids.
Please suggest.
Thanks,
Anukul
"Hilary Cotter" wrote:

> If your schema is static, and your users will never be accessing these
> databases at the same time or if you can minimize conflicts using
> partitioning you could use bi-directional transactional replication.
> Otherwise you should look at merge replication.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <Anukul@.discussions.microsoft.com> wrote in message
> news:10746DEA-A21D-4702-A44B-B46631DC89FC@.microsoft.com...
> DB
> both
> troubleshoot
>
>
|||merge replication will probably work best for you then.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <Anukul@.discussions.microsoft.com> wrote in message
news:0A5E7A9E-43E7-43DC-8F45-0926FD654F0C@.microsoft.com...
> Thanks Hilary!
> Yes the Database structure is static, but may change once every 1 month.
> Also, users will be accessing the DB simultaneously at both ends, as
Inbound
> customer calls will be received at both sites, so changes relating to a
> customer's address, name, order may get updated. I am over looking the
> scenario where same customer calls at same time at both sites, but the
same[vbcol=seagreen]
> customer's data may be worked on at both sites at same time. My ASP
> application will change as it uses identities right now, but will I will
> create site specific ids.
> Please suggest.
> Thanks,
> Anukul
> "Hilary Cotter" wrote:
own[vbcol=seagreen]
day.[vbcol=seagreen]
|||Thanks Again!
Is there a Book which you can recommend to setup Merge Replication?
Are there any 3rd party tools for Conflict Resolution available?
Please guide.
Thanks,
Anukul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eqXWnHp8EHA.2876@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> merge replication will probably work best for you then.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <Anukul@.discussions.microsoft.com> wrote in message
> news:0A5E7A9E-43E7-43DC-8F45-0926FD654F0C@.microsoft.com...
> Inbound
> same
their[vbcol=seagreen]
> own
replicate/snycronize[vbcol=seagreen]
every
> day.
>
|||Well I am working on a merge book. There are no third party tools I know of.
The default conflict resolvers which ship with SQL Server are pretty good.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <anukuls@.yahoo.com> wrote in message
news:u961qLp8EHA.3988@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks Again!
> Is there a Book which you can recommend to setup Merge Replication?
> Are there any 3rd party tools for Conflict Resolution available?
> Please guide.
> Thanks,
> Anukul
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eqXWnHp8EHA.2876@.TK2MSFTNGP12.phx.gbl...
month.[vbcol=seagreen]
a[vbcol=seagreen]
will[vbcol=seagreen]
these[vbcol=seagreen]
replication.[vbcol=seagreen]
same
> their
> replicate/snycronize
> every
>
|||Hi Hilary:
Just another query:
As I mentioned , what would be the best way of adding / dropping columns to
an existing Database, which is published for replication (merge
replication)?
Would sp_addrplcolumn & sp_deleterplcolumn work or are there any other ways
to it ? Even datatype changes or size changes.
Thanks,
Anukul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eu7mcWp8EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Well I am working on a merge book. There are no third party tools I know
of.[vbcol=seagreen]
> The default conflict resolvers which ship with SQL Server are pretty good.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <anukuls@.yahoo.com> wrote in message
> news:u961qLp8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> month.
to[vbcol=seagreen]
> a
the[vbcol=seagreen]
the
> will
> these
> replication.
> same
>
|||sp_repladdcolumn and sp_repldropcolumn are the best ways.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <anukuls@.yahoo.com> wrote in message
news:O59cEGA9EHA.3640@.tk2msftngp13.phx.gbl...
> Hi Hilary:
> Just another query:
> As I mentioned , what would be the best way of adding / dropping columns
to
> an existing Database, which is published for replication (merge
> replication)?
> Would sp_addrplcolumn & sp_deleterplcolumn work or are there any other
ways[vbcol=seagreen]
> to it ? Even datatype changes or size changes.
> Thanks,
> Anukul
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eu7mcWp8EHA.3236@.TK2MSFTNGP15.phx.gbl...
> of.
good.[vbcol=seagreen]
as[vbcol=seagreen]
> to
> the
> the
ASP[vbcol=seagreen]
I[vbcol=seagreen]
using[vbcol=seagreen]
access[vbcol=seagreen]
once[vbcol=seagreen]
orders,
>
|||Also, have a look at an article on
www.replicationanswers.com for methods of changing an
existing datatype, and Kestutis's post from yesterday.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Please help...

Any suggestions? Can anyone set up the simplest of replication from SQL
7.0 pub/dist to SQL2000 subscriber?
thanks a lot
Yes, it should work fine. What you do is go register your sql 2000
subscriber in EM, and then connect to the publisher in EM, and go to tools,
replication, configure distributor, publishers, and subscribers and add your
sql 2000 subscriber 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
"sam" <sam@.discussions.microsoft.com> wrote in message
news:60005DC2-8C97-4502-98F1-5A16229D2A86@.microsoft.com...
> Any suggestions? Can anyone set up the simplest of replication from SQL
> 7.0 pub/dist to SQL2000 subscriber?
> thanks a lot