SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL database not accessible during transactional replication


SQL database not accessible during transactional replication

Author
Message
happycat59
happycat59
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8821 Visits: 3281
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.



p.megens
p.megens
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 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?
Roy Ernest
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8837 Visits: 6891
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
srihariguruprasad_t
srihariguruprasad_t
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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
srihariguruprasad_t
srihariguruprasad_t
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search