[SQL] SQL Server Deadlocking: What is TranactionName 'GetInitializeIMA' refer to?

I have recently seen a number of deadlocks occurring within our database and it's not clear as to why.

Firstly after setting the deadlock_priority to a value of 10 within the SProc to give the proc an advantage over others, it is still being chosen as the victim. The proc does carry out a number of ALTER PARTITION commands and although the advised trace flag has also been placed in the proc, the code is falling foul of a deadlock.

I have to say not all the time though. There is only one other transaction seen that does come out the winner everytime, and it isn't yet clear as to why. The other proc has a normal deadlock priority value so you would expect it to be killed off. There are times when the 'killer' proc does lose and all is 'fine'. However when it does win, it always has a "TransactionName='GetInitializedIMA'". I have no idea what that transaction is and why, when seen, it will always kill over my Priority 10 proc.

Anybody know what this TransactionName refers to? I do know that the proc has a SCH-M lock and is waiting for the SCH-S lock that my priority 10 proc has, and that in turn is after the SCH-M lock which would cause a deadlock, but why does the GetInitializedIMA transaction win?

Thank you

We have increased the deadlock priority all the way up from 0 to 10 and has made no difference. We have also added the Trace Flag to the code 1237, but that doesn't resolve this issue either.

Continue reading...