by RMD
16. March 2011 13:28
There was a nasty bug in SQL Server 2008 RTM and SP1 that could result in a deadlock when you attempted to create a new schema object, such as a stored procedure, that used a user defined data type that was created in the same transaction. The error doesn’t mention the data type at all. Instead, it’s the normal deadlock error as seen below:
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
This bug was reported on 9/3/2008 on Microsoft Connect. and they fixed the issue in SQL Server 2008 SP2, but they decided not to include the fix in SQL Server 2008 R2. So scripts that run great on 2008 SP2 will fail on R2. Wonderful.
Thankfully, the work around is an easy one. You just have to make sure your data type is created outside of the transaction that creates the objects that use it. This makes failed script cleanup a bit harder, but it’s not the end of the world.