 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Replication / August 2006Replication became one-way only
SQL 2000: I have a transactional replication with an updatable subscriber. The update method at the subsrciber is set to "queued" (otherwise inserts on the subscriber will fail in case the publisher is off). This has been working fine until yesterday 14:19. Since then inserts on the subscriber aren't replicated back to the publisher anymore. Inserts on the publisher still show up on the subscriber. I didn't change anything in the config, but yesterday afternoon I inserted a million records on the subscriber. Today I tried to stop/start the queue, log, distribution agents, but no effect. How can I get this replication back to normal? Since my client apps are currently writing to the subscriber, a new snapshot isn't really a good idea, and I want to avoid a switchover on the client apps. Have a look at Subscriber Server settings. It should have 'Allow triggers to be fired which fire other triggers (nested triggers)' checked. > SQL 2000: I have a transactional replication with an updatable subscriber. > The update method at the subsrciber is set to "queued" (otherwise inserts [quoted text clipped - 13 lines] > to the subscriber, a new snapshot isn't really a good idea, and I want to > avoid a switchover on the client apps. Let me guess, you inserted them via DTS. The queued triggers will not fire. You will have to somehow try to insert them again.  SignatureHilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com > SQL 2000: I have a transactional replication with an updatable subscriber. > The update method at the subsrciber is set to "queued" (otherwise inserts [quoted text clipped - 13 lines] > to the subscriber, a new snapshot isn't really a good idea, and I want to > avoid a switchover on the client apps. The nested triggers are allowed. For the million record job I did not use DTS, just simple SQL statements: INSERT INTO table1 (a, b, c) SELECT a, b, c FROM annotherDB..table1 Deleting the million records from the subscriber and inserting them again won't be a problem, but I'd have to get the replication fixed first. Right now, even single records are not replicated from the subscriber back to the publisher, but other way round it still works. Which processes are actually maintaining the queues? Is there anything I should restart? The DB is operational, so I need to minimize the impact of any restart. Additional info: I looked a bit into the triggers on the subscriber. They are firing and data is piling up in MSreplication_queue. This table is growing with every insert made on the database. Have you checked that are there any records in the 2 tables below of Subscriber msrepl_queuedtraninfo msreplication_queue > SQL 2000: I have a transactional replication with an updatable subscriber. > The update method at the subsrciber is set to "queued" (otherwise inserts [quoted text clipped - 13 lines] > to the subscriber, a new snapshot isn't really a good idea, and I want to > avoid a switchover on the client apps. Yes, the number of records in MSreplication_queue matches the number of inserts that haven't been replicated yet (ca 1.9M) and MSreplication_queue has approx 32k records. Both are increasing with every insert made on the database. > Have you checked that are there any records in the 2 tables below of > Subscriber > msrepl_queuedtraninfo > msreplication_queue Refer to the link below for Queue Reader Agent Logging to see details http://support.microsoft.com/default.aspx?scid=kb;en-us;312292&sd=tech > Yes, the number of records in MSreplication_queue matches the number of > inserts that haven't been replicated yet (ca 1.9M) and MSreplication_queue [quoted text clipped - 5 lines] >> msrepl_queuedtraninfo >> msreplication_queue ok, switched on the -output option by changing step2 in the properties of the Queue Reader agent. Getting lots of the following even though the client apps are constantly inserting records at the subscriber: No queued transaction available [8/29/2006 4:58:23 PM]PTSALISQL.distribution: exec master.dbo.sp_MSenum_replsqlqueues N'distribution' [8/29/2006 4:58:23 PM]PTSALISQL.distribution: exec master.dbo.sp_MSenum_replqueues N'distribution' PTSALISQL is the publisher and distributor. One more thing: when I edited the "run agent" step as mentioned above, the "database" drop down list always defaults to the first entry in the list. Seems to me that I need to select the distribution DB for the agent to start up correctly. Any relevant info gained from sp_getqueuedrows ? Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com Did this query with the smallest table I have. The records shown match my expectation, i.e. they are the missing replications. Interesting is that some of the records show only NULL in the fields that are supposed to show the values. This happens for INS and DEL even though I never inserted records with only NULL values. The replication is working again. Here is what I did: - stop the queue reader agent - insert a "return" at the top of all insert triggers on the subscriber (this basically disables the replication, the other triggers aren't used in my case) - delete everything from MSreplication_queue and MSrepl_queuedtraninfo - restart the queue reader agent - remove those return statements from the triggers This may not be the most elegant way, but it worked. Now I have some cleanup to do in order to manually "replicate" the missing data. :-( Why did this problem happen in the first place? Overload? Are there limits as to how many records you can write into a subscriber with queued replication to the publisher? Dirk - very difficult to say what the issue was. If you have TEXT/Image datatypes then I can see there would be an issue - is this the case? If not, then the next time it occurs (hopefully never!) I'd raise a PSS call, as there might be a relevant hotfix somewhere for this. Rgds, Paul Ibison The tables where I inserted data don't have any big fields, just some varchar, datetime, etc with less than 250 bytes per record. My biggest table also has an varchar(1024) and an nvarchar(1024), but I haven't touched those yet. Only the number of records was high at a million records. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.