Re: Need latency help for SQL 2K

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks. I will do that. I did a manual look see at them last week and saw a
ton, but I am still not sure if it is related to the actual data changes or
possibly some network/hardware issues. Especially since the developers are
claiming that nothing has changed (and the commands I see stuck in
replication do not seem out of the ordinary.) Bascially I don't know how to
determine "where" the problem is occuring to see what I need to fine tune.
And if it is a new batch causing the problem, I'm not sure where I can fix
that given it is probably needed. We have always run batch stuff in the
evening (that HAS to be done) and it has never caused this problem before.

Are there any additional tweaks I can do in the agents to speed up
replication? Anywhere else?

--Kristy

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:2700612F-87C6-4CFD-A0F7-7AB670A35267@xxxxxxxxxxxxxxxx
You can pipe sp_browsereplcmds to a results table (its the @results_table
paramter). I suspect you have a batch operation which is generating a
massive amount of inserts/updates/deletes and replication has to process
all these commands.

"Kristy" <forums@xxxxxxxxxxxxxxxx> wrote in message
news:OE1F5vvqIHA.4884@xxxxxxxxxxxxxxxxxxxxxxx
Anybody have any idea of things I can check with regards to latency on my
replicated servers?


I have a production database with an MDF about 170GB and lots of
continuous activity. All servers are running SQL Server 2K with SP4 on
them. We use Transaction Replication to 2 servers - 1 at the same
location as the production server and 1 in a different state.

For the last few months, I have noticed that during the middle of the
night, nothing seems to be replicating and thus latency builds up. I have
latency jobs in place to record the time to a text file, and thus I can
nail it down to an approximate time that things start going haywire. By
morning, latency is usually 11-12 hours behind and takes all day to catch
up to its normal couple of second latency time. No one will admit to me
what might have changed on the app side or if there is some other issue
going on, so I have to track it down myself.

Any suggestions?

Thanks,
Kristy





.

Relevant Pages

  • BCPBatchSize and Replication hanging
    ... I have transactional replication setup between 2 sql 2000 sp3a servers ... The Distribution runs every 10 minutes and normally keeps up just fine. ... I have enabled the tsql batch start and end to trace the ...
    (microsoft.public.sqlserver.replication)
  • BcpBatchSize problem in replication
    ... I have transactional replication setup between 2 sql 2000 sp3a servers ... The Distribution runs every 10 minutes and normally keeps up just fine. ... I have enabled the tsql batch start and end to trace the ...
    (microsoft.public.sqlserver.server)
  • Re: Need latency help for SQL 2K
    ... I suspect you have a batch operation which is generating a massive amount of inserts/updates/deletes and replication has to process all these commands. ... I have a production database with an MDF about 170GB and lots of continuous activity. ... All servers are running SQL Server 2K with SP4 on them. ... I have latency jobs in place to record the time to a text file, and thus I can nail it down to an approximate time that things start going haywire. ...
    (microsoft.public.sqlserver.replication)
  • Re: Need latency help for SQL 2K- Hilary
    ... And if it is a new batch causing the problem, I'm not sure where I can fix ... massive amount of inserts/updates/deletes and replication has to process ... All servers are running SQL Server 2K with SP4 on ... nothing seems to be replicating and thus latency builds up. ...
    (microsoft.public.sqlserver.replication)
  • Remove all copies of the same record from merge-replicated table in multiple databases
    ... Let say we have a table called tbProductionBatch in database ... servers in three branch offices. ... we want to get rid of all the copies of this "batch" record from all ... rather than waiting for merge replication to kick in). ...
    (microsoft.public.sqlserver.replication)