SQL database not accessible during transactional replication

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SS2K5 Replication » SQL database not accessible during...

15 posts, Page 2 of 2

««1 2

SQL database not accessible during transactional replication

Rate Topic

Display Mode

Topic Options

Author

Message

happycat59

Posted Wednesday, March 03, 2010 9:04 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:45 PM
Points: 2,471, Visits: 2,065

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

p.megens

Posted Thursday, March 04, 2010 7:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 12, 2012 4:33 AM
Points: 6, Visits: 31

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

Roy Ernest

Posted Thursday, March 04, 2010 7:49 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:52 AM
Points: 3,280, Visits: 6,622

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

srihariguruprasad_t

Posted Tuesday, January 10, 2012 11:16 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 11:09 PM
Points: 1, Visits: 11

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

srihariguruprasad_t

Posted Tuesday, January 10, 2012 11:24 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 11:09 PM
Points: 1, Visits: 11

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 »

15 posts, Page 2 of 2

««1 2

Permissions