Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL database not accessible during transactional replication Expand / Collapse
Author
Message
Posted Wednesday, March 3, 2010 9:04 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:22 PM
Points: 3,064, Visits: 2,676
I would be checking for blocking. Replication is attempting to deliver changes to about 10% of the records - to me that sounds like you probably end up with replication having exclusive locks on most of the table(s) in question.

Since you have a slow connection, have you looked using a different profile for the distribution agent. One of the changes that you can make is to decrease the bcpbatchsize. From memory it is every 100,000 records. On a slow network could take quite a while. I would decrease this to something like 10,000.



Post #876509
Posted Thursday, March 4, 2010 7:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:45 AM
Points: 8, Visits: 38
I've tested today with replication of only 10.000 changed records. Even this small amount of records will give timeout errors on the Stored Procedures that are used by the website.

@Roy, how can I see what kind of WAIT Types are happening while the bulk updates are being replicated? Somewhere in the logfiles?

Post #876831
Posted Thursday, March 4, 2010 7:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
There is a very good blog by Jimmy may with some SQL Queries that gives you Wait Type, Finds out if there are IO issues, Blocking etc. Check this blog.
In that page there is a link called "DMV All-Stars Dream Team"
You can run the SQL Query based on your SQL Server Version. What I would look for if I were you would be REAL TIME BLOCKING and the Virtual File latency. The latter one will show if there are any problems with your IO subsystem.


-Roy
Post #876846
Posted Tuesday, January 10, 2012 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 2, 2014 9:38 PM
Points: 1, Visits: 12
I don't think snapshot is the correct replication to use in this case as it locks the tables and other articles for publishing during replication process. Transcational and Merge do not held locks on the articles and your website/application can still use last committed snapshot data. This is my understanding. Can someone correct me if this is not the case?

http://msdn.microsoft.com/en-us/library/ms151734.aspx
Post #1233730
Posted Tuesday, January 10, 2012 11:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 2, 2014 9:38 PM
Points: 1, Visits: 12
I don't think snapshot is the correct replication to use in this case as it locks the tables and other articles for publishing during replication process. Transactional and Merge do not hold locks on the articles and your website/application can still use last committed snapshot data. This is my understanding. Can someone correct me if this is not the case?

http://msdn.microsoft.com/en-us/library/ms151734.aspx
Post #1233732
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse