.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

2 different publications deadlocking each other?

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
Hi all sql2005 transactional replication On one of our publishers there are 4-5 small publications where the data doesn't change too often replicating to a single subscriber. Lately all of them re-try the alloted 10x and write an entry to the application log stating that a deadlock was encountered. Lets call this publisher A and subscriber A. Now I realise this does happen every so often since other apps may lock the tables etc. We have another publisher which replicates all our reference data to all servers. Lets call this publisher B and it replicates to subscriber A too. When I investigated subscriber A's process's in activity monitor it seems (at random) that replication processes from publisher B are blocking processes from publisher A? These publications/subscriptions go to the same subscriber database but the actual tables have nothing to do with one another. Is there a system replication table that is causing a problem or something along those lines? Publisher C exists in same server location as publisher A and has exact same publications (different content) replication to a different subscriber in same server location as subscriber A. Publisher B also replicates same data to subscriber C. None of the above errors are occurring in Pub C to Sub C replication. Hence I'm confused, what am I missing or overlooking?

View Complete Post

More Related Resource Links

Excessive Deadlocking Reporting Services 2008 Integrated.

Hi, we have a major issue with deadlocking on our reporting Server TEMPDB (REPORTSERVERTEMPDB). The platform is a Sharepoint Integrated platform allong the following lines 2 WFE Servers (NLB) 2 Sharepoint application servers 2 SQL Reporting Services (NLB) Cluster hosting Sharepoint DB's Cluster hosting Reporting Services DB's and the Data. This error is occuring for both manually running reports via sharepoint and load testing, and is happening on our testing platform as well which is slightly different in configuration. Reported back to the users is "An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database" Running a profile with 1224 set, we are finding deadlocks on the REPORTSERVERTEMPDB on PK_SNAPSHOTDATA. We have tried the following 1. Dropping the PK - The deadlock was then showing an OBJID which we could not identify. 2. Change the database properties to READ-COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION to ON 3. Re-create and change the PK to ALLOW_PAGE_LOCKS OFF None have these have had an impact on the issue This has become apparent since the following actions were taken 1. The setting of caching has been enabled by the developers 2. The Report Server DB's were moved from the Sharepoint Cluster to the Data Cluster. I do have an XDL file from Profiler but it is to

Deadlocking in stored proc calls (sql 2008)


The deadlock revolves around 3 tables (A, B, and C), shown simplified below.


Table “A”:


     [A_ID] [bigint] NOT NULL,

     [A_DeleteID] [bigint] NULL,


Tracking of schema changes replication has stopped working in some of our publications


Environment Summary

Our environment is SQL Server 2008 R2 running Merge Replication with Web Synchronization using the SQL Server Compact Edition 3.5 SP2 Server Tools, and SQL Server Compact Edition 3.5 SP2 for the client DBs on full Windows XP or later OS’s.  We have about 120 articles, each of which range from 10 published columns to close to the 255 limit. Regarding join filtering, we have a root article filtered on the hostname, which then has 2 levels of filtering cascading off that.  We are using the default Row Level change tracking.


The Problem

Tracking of schema changes replication has stopped working in some of our publications

The manifestation is that subscriptions schemas are left behind at some point in time, and the only temporary recovery has been to delete the client DB and get it to recreate from a snapshot which then contains the latest schema.  However, subsequent schema changes don’t propagate.

The longer term workaround that we have applied has been to manually update the sysmergesubscriptions table on the publication DB and set the schemaversion column values (ALL of which are inexplicably high in this

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend