July 30, 2008 at 7:05 am
Hi,
We upgraded the servers from SQL 2000 to SQL 2005.
Server A is having 30 databases. Server B is having 25 databases.
In Server B 15 databases are replicated from Server A.
My All applications wil retrieve the data from replicated server Server B, then updating in Server A.
Note: We have Transactional Replication.
When users are retrieving the data from Server B, who is accessing the Server A users facing Dead Lock Error (Transaction Dead lock victim) very often.
How Can i solve this issue?
July 30, 2008 at 7:12 am
By Acessing you mean r they just Selecting(DDL Statements) or modifying it too(DML statements)?
July 30, 2008 at 7:30 am
They are performing DML statements.
July 30, 2008 at 7:30 am
The first step to solving deadlocks is finding the cause. Two ways to do that, you can set up a trace and monitor the deadlock graph events, second you can enable either the 1204 or 1222 traceflags (DBCC TRACEON(1222,-1)) and the deadlock graph will be written into the error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2008 at 7:31 am
S.
In Server B - DDL Statements
In Server A - DML Statements
July 30, 2008 at 7:35 am
Mayank Khatri (7/30/2008)
By Acessing you mean r they just Selecting(DDL Statements) or modifying it too(DML statements)?
Selects are also classified DML statements. DDL are data definition statements, things like create table, alter procedure and the like
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2008 at 7:40 am
In Server B - Select statement only
In Server A - Insert, Delete, Update statements wil run.
July 30, 2008 at 8:15 am
Is this one-way or two-way replication? Is it Immediate Updating or Queued?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 30, 2008 at 8:55 am
Have u tried reading dirty transactions in ServerB (i.e. uncommited) by setting query hint (nolock) or for a connection setting transaction isolation level READ UNCOMMITED
July 30, 2008 at 10:58 pm
It is one way replication and Immediate updated Transaction Replication.
queries used read uncommitted isolation level to retrive or updating the data.
July 31, 2008 at 7:03 am
Immediate Updating can contribute to distributed deadlocks like you are seeing. If you are going to stick with Replication, then I would recommend either switching to Queued updating or Merge replication. Note that these options usually involve some small lag time between the update on the production dataabse and when that update happens on the replicated database.
Of course if you are using this for reporting, then there are other options that may be better than Replication. Log Shipping is my preference, unless you need the reporting database to have no lag from the production data. Then you might conside Database Mirroring (actually, I cannot rememebr if you can use the mirror for reporting or not).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 31, 2008 at 9:52 am
rbarryyoung (7/31/2008)
(actually, I cannot rememebr if you can use the mirror for reporting or not).
You can, using database snapshots. The mirror DB itself is not usable (recovering).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2008 at 8:46 am
See this series for the definitive word on deadlock troubleshooting and resolution: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply