November 30, 2007 at 10:34 am
my transactional replication is
taking over 15 hours to complete! This is my first time setting up
replication so maybe I am missing something.
Details:
Sql Server 2000 (all patches and sp up to date)
Publisher and Distributor - Server A
Subscriber - Server B
Distribution Agent runs every night at 5pm and is still running in the
middle of the next day.
5 tables are published with 2 tables being filtered vertically.
The log jam seems to be at Server B. Server B is a db server for a
web site so gets hits at any time of day. CPU on server B is at a
resonable rate as is the amount of memory being used. Nic card is
1GB.
I upped the time out interval on the agent profile to stop time out
errors occuring.
msrepl_transactions currently have 4960 commands waiting
(approximately one days worth of work). Is that a large amount of
commands?
Also, I notice in the current activity when the replication is running
that the replication connections are blocking other connections and
sometimes vice versa.
When the replication runs it greatly affects the speed of the searches
on the website so it is a real problem.
Any input would be deeply appreciated.
Thanks in advance
Emmeline
November 30, 2007 at 11:20 am
Change your isolation-level to read-uncommitted for ALL select statements at subscriber.
You can do SET ISOLATION LEVEL READ-UNCOMMITTED (see bol) at top of each sp - or - in from clause use (nolock) e.g. select * from mytable (nolock) where blah = 'blahblah'
Also - consider running your distribution agent a little more frequently than 1 x per day - set to continuous or every minute & see how that does... 4600 is nothing - your problem is definitely contention - also - make sure you queries at subscr are optimized & using indexes - no table or clust idx scans...
ChrisB MCDBA
MSSQLConsulting.com
Chris Becker bcsdata.net
December 5, 2007 at 1:33 am
Hi,
I totally agree.
It is quite reasonable to have replication to a DB used by a website..we do it. Ours gathers user data i.e. what they searched etc. The DIST agent runs once every 10 mins..
Having nolocks on your selects will definitely help.
Not sure what you are replicating but I think it is always best to have the data as static as possible. If you need to replicate data out then perhaps a staging database might be better..that way you can control the data going to the live data during quiet times but still get data out to the live server.
Regards
Graeme
December 10, 2007 at 9:21 am
Sorry for the slow response, have been away. Thanks so much for the input, will try it and post results.
Emmeline
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply