SQL Deadlock Victim on 2008 R2

skling

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: xxx-xxx-xxxxxx-xxxx
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

Brian Donahue

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.

-or-
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.

skling

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.

Brian Donahue

Either a linked server or a plain-old distributed query (with a three-part identifier).

skling

We have no distributed queries or linked servers. Still owe a verbose log on this. Had to set it down for a couple days.