SQL Deadlock Victim on 2008 R2

Forum for users of SQL Compare schema synchronization utility

SQL Deadlock Victim on 2008 R2

Postby skling » Wed Apr 24, 2013 4:13 pm

My 2008 R2 SQLServer instance simply will not build SQL Compare due to a deadlock. The only difference between this agent instance and the others is that its R2 and has 4 processors (vs 2). I find nothing posted in the last two years on this and wondering what might be happening. Is it simply going too fast? Is there a retry interval?

Is it R2?

Here's the output:

[echo] SQL Compare Argument Line: /scripts1:C:\\bamboo-agent\\xml-data\\build-dir\\HANOTIFICATION-DEPLOY2TARGET-RC2TARGET\\HA_Notification\\dbschema /db2:HA_Notification /server2:bamboo-agent1.hahosting.local\\amiller /options:ip /sync
24-Apr-2013 11:02:06 [exec] SQL Compare: activated, edition: professional, serial number: 507-031-178161-14A7
24-Apr-2013 11:02:06 [exec] SQL Compare Command Line V10.3.8.406
24-Apr-2013 11:02:06 [exec] ==============================================================================
24-Apr-2013 11:02:06 [exec] Copyright c Red Gate Software Ltd 1999-2012
24-Apr-2013 11:02:06 [exec]
24-Apr-2013 11:02:06 [exec] Registering data sources
24-Apr-2013 11:02:10 [exec] Creating mappings
24-Apr-2013 11:02:10 [exec] Comparing
24-Apr-2013 11:02:10 [exec] Applying Command Line Items
24-Apr-2013 11:02:10 [exec] Retrieving migration scripts
24-Apr-2013 11:02:10 [exec] Checking for identical databases
24-Apr-2013 11:02:10 [exec] Creating SQL
24-Apr-2013 11:02:11 [exec] Deploying changes (from DB1 to DB2)
24-Apr-2013 11:02:12 [exec] Error: Synchronization of 'Scripts.dbschema' and
24-Apr-2013 11:02:12 [exec] 'bamboo-agent1.hahosting.local\\amiller.HA_Notification' failed: Transaction
24-Apr-2013 11:02:12 [exec] (Process ID 51) was deadlocked on lock resources with another process and has
24-Apr-2013 11:02:12 [exec] been chosen as the deadlock victim. Rerun the transaction.
24-Apr-2013 11:02:14 [exec] C:\\git\\deploy\\redgate.build(257,4):
24-Apr-2013 11:02:14 [exec] External Program Failed: C:\\Program Files (x86)\\Red Gate\\SQL Compare 10\\SQLCompare.exe (return code was 126)
24-Apr-2013 11:02:14
24-Apr-2013 11:02:14 BUILD FAILED - 1 non-fatal error(s), 4 warning(s)

Microsoft SQL Server Management Studio 10.50.1600.1
Microsoft Analysis Services Client Tools 10.50.1600.1
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.10.9200.16540
Microsoft .NET Framework 2.0.50727.5466
Operating System 6.1.7601
Posts: 7
Joined: Thu Oct 11, 2012 8:50 pm

Postby Brian Donahue » Mon Apr 29, 2013 12:40 pm

Thanks for your post.
The last time this happened, it was something to do with a linked server pointing back to the same database. That's sort of hard to troubleshoot, but I think if you enable verbose logging you should be able to get the name of the object causing the problem and we can work from there.

1. add /LogLevel:verbose to the command.
2. check %userprofile%\\appdata\\local\\red gate\\logs\\sql compare 10 for the most recent log file.

you could try adding /transactionIsolationLevel:"READ COMMITTED" and see if that helps. This runs the schema update without exclusive locks, which is dangerous from a consistency point-of-view, but may get the update working.
Brian Donahue
Posts: 6590
Joined: Mon Aug 23, 2004 9:48 am

Postby skling » Mon Apr 29, 2013 2:43 pm

Thanks for the reply.

No linked servers in this target environment. We'll play with the transaction read modes, up the log verbosity and report back.
Posts: 7
Joined: Thu Oct 11, 2012 8:50 pm

Postby Brian Donahue » Fri May 03, 2013 3:34 pm

Either a linked server or a plain-old distributed query (with a three-part identifier).
Brian Donahue
Posts: 6590
Joined: Mon Aug 23, 2004 9:48 am

Postby skling » Mon May 13, 2013 10:14 pm

We have no distributed queries or linked servers. Still owe a verbose log on this. Had to set it down for a couple days.
Posts: 7
Joined: Thu Oct 11, 2012 8:50 pm

Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests