Replication taking longer

I've got once a day snapshot replication working between 2 servers. I
take a copy of most of a database for MI and adhoc query processing
purposes. This has been working okay for several months now but I've
noticed that the time to load the database is gradually taking longer.
I don't think this is a product of the source database growing, because
whilst it has grown by 2-3%, the time taken to load the target database
is taking 80-90% longer than it did a month ago. I thought this may be
due to some physical file structure problem and I reindexed all tables
with no effect. I've also noticed that a DTS package that I'm using to
copy a much smaller database across to the same MI server has also
increased in duration over the same time period.
Any ideas to solve this would be welcome.
TIA
Laurence Breeze
Laurence,
indexing the tables wouldn't speed up the snapshot application at the
subscriber - actually quite the opposite . Perhaps you can monitor which
part of the process is taking more time than expected. You could just
monitor it using dbcc inputbuffer at the subscriber while it processes to
get a feel for it. If the DTS task has increased in time then it could be
network issues or the hardware on the subscriber. Loads of possibilities
here. You could copy a file to the server to test the network transfer
speed. I'd also monitor the disk writes/sec as maybe the problem lies there.
Disk fragmentation could be worth looking at, but I wouldn't expect this to
be such a major factor. Finally, check the general performance of the server
outside of data transfers - just adding data to a dummy table - and compare
to another (similar) server.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication Query Problem

SELECT DISTINCT MH.agent_id,
MA.subscriber_name,
MH.publisher_insertcount,
MH.publisher_updatecount,
MH.publisher_deletecount,
MH.publisher_conflictcount,
MH.subscriber_insertcount,
MH.subscriber_updatecount,
MH.subscriber_deletecount,
MH.subscriber_conflictcount,
MAX(start_time) AS LastSync
FROM Distribution.dbo.MSMerge_History MH INNER JOIN
Distribution.dbo.MSMerge_Agents MA
ON (MA.id = MH.agent_id)
WHERE runstatus = 2
AND LEN(subscriber_name) > 0
AND MA.publication = 'JCS'
GROUP BY agent_id, subscriber_name, runstatus, publisher_insertcount,
publisher_updatecount,
publisher_deletecount, publisher_conflictcount,
subscriber_insertcount, subscriber_updatecount,
subscriber_deletecount, subscriber_conflictcount
ORDER BY subscriber_name ASC
When I run the above query, I get back the expected results. However, I am
getting two rows for one subscriber in particular, and when I executed the
Query in QA, I saw that the agent_id for both of those rows is the same.
Both rows show the replication as being successful; the times ran were only
two minutes apart. What am I missing? I was under the understanding that
specifying DISTINCT in your SELECT ensured that you would only get unique,
non-matching rows returned. You can also see that I am selecting the most
recent (MAX) start_time in my query. Any helpers out there as to why this
would occur?
Any help is MUCH appreciated!
Best Regards,
Brad
Is all of the data exactly the same including the insert/update/delete
counts?
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||No, the SyncDate and counts are different for each row. But I was under the
impression that using MAX, as well as DISTINCT would ensure that I would not
get duplicate rows (note that when I get multiple rows, the agent_id for the
duplicate rows is the exact same).
Any help with/explanation regarding this would be greatly appreciated Mike!
Best Regards,
Brad
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:uepgBvGIEHA.3200@.TK2MSFTNGP10.phx.gbl...
> Is all of the data exactly the same including the insert/update/delete
> counts?
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>

Replication Query

Dear friends

I want to know one thing in replication.I try to apply all three types of replication.in all these whenever I start initilization it is trying to drop & create database objects.I only want to only replicate data.Most of the times i am getting error like this' this particular table//view is not able to drop it's in replication'.If i am selecting no trnsfer of data & schema.Then it's giving error like that 'snapshot sequence is not correct' when synchronization.So please tell me some solution for this
Thanks in Advance
FilsonHai friend

I hope this link helpfull for u
http://docs.openlinksw.com/virtuoso/SNAPSHOT.html

Replication Progress

How can I see what the pending transacions are for a given server? Is
there an app that will show me this, or do I have to query a table?
TIA,
Larry...
If you are talking about transactional replication, sp_browsereplcmds can be
used. To filter out commands already replicated, you have to provide the
@.xact_seqno_start argument. The relevant value will be in the
MSreplication_subscriptions table - transaction_timestamp column.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Replication Logreader query

We had replication set up and working successfully for 2+ years between an intranet server and a webserver (offsite).

The web server was moved and the replication went out of sync. :mad:

Now when we try the replication it is trying to start from scratch and as you can imagine there is quite a lot of data after two years!

I have scheduled the logreader to run but after 8 hours it is still running is there any way to find out how long it should take?

We have removed any unnecessary data to try to help speed up the process.

Help appreciatedYou probably need the snapshot agent instead of the logreader agent. It takes the starting "snapshot" of the database to send to the subscriber, to restart replication.

-PatP|||We ran the snaphot first thing this morning - job outcome was successful.

Getting this message when we look at the snapshot step details for the run agent step:

"A snapshot was not generated because no subscriptions needed initialization. The step succeeded."

Any ideas on how to find out how long the logreader will take?|||Did you reinitialize the publications? That will flag the publication for a new snapshot. Just FYI, you probably want to script the indicies and the permissions on the subscriber, since the replicated tables will be destroyed and recreated as part of the process.

-PatP|||We have reinitialised the publications.

Thanks for the tip on indexes etc.

Any tips on finding out how long to complete the logreader and then distribution tasks? :confused:|||The logreader is eternal, if replication is going on, the logreader is running. The snapshot agent usually doesn't run too long, it simply copies the data from the published tables into flat files, but its performance is VERY dependant on your hardware (fast hardware/short runtime, slow hardware/well, you know that drill).

The distribution task depends on way too much stuff for me to take a guess at its performance... The network connection, the subscriber performance, the distributors disk, and about a half a gazillion other factors come into play.

Once the tasks start running, you can sometimes get better ideas about the performance, but knowing nothing about your configuration I can't even hazard a guess!

-PatP|||Pat

Distribution is executing - looks like it will be a long night :)

Thanks for your help - it is greatly appreciated

Cheers|||If you wander down to the distribution agents in your replication monitor (on the distributor), you can at least watch the "paint dry" via the status messages. It ain't much, but it is better than nothing!

-PatP