Last night I implemented a SQL Script to create three tables, nothing out of the extraordinary, primary keys and foreign keys defined. I tested it before hand OK. I implemented it in production and it took approx. 20+ seconds to finish. While I was waiting I started to freak-out as it is unusual for a scripts to create db objects to take this much time. I quickly looks though red-gate to see if there was any locking and to my surprise I found dead-locks.  The script in question did not populate any tables but it has a foreign key constraint to the table involved in the dead-lock.

Unfortunately the victim was an update to the table in question from our application and my create table script won over the application update. 

I am now thinking that I should always set my session SET DEADLOCK_PRIORITY to LOW before implementing any scripts.

What I don't understand is why and what is SQL Server doing on the background to cause the dead lock on creating table objects.

Red-gate was able to capture one of the sql script fragments involved in the dead-lock, I don't know if its from the update session that got killed or if it's from the script for the table creation.

Is any body able to help?

Here it is:

(@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000))SELECT
clmns.name AS [Name],
clmns.column_id AS [ID],
clmns.is_nullable AS [Nullable],
clmns.is_computed AS [Computed],
CAST(ISNULL(cik.index_column_id, 0) AS bit) AS [InPrimaryKey],
clmns.is_ansi_padded AS [AnsiPaddingStatus],
CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],
CAST(ISNULL(COLUMNPROPERTY(clmns.object_id, clmns.name, N'IsDeterministic'),0) AS bit) AS [IsDeterministic],
CAST(ISNULL(COLUMNPROPERTY(clmns.object_id, clmns.name, N'IsPrecise'),0) AS bit) AS [IsPrecise],
CAST(ISNULL(cc.is_persisted, 0) AS bit) AS [IsPersisted],
ISNULL(clmns.collation_name, N'') AS [Collation],
CAST(ISNULL((select TOP 1 1 from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), 0) AS bit) AS [IsForeignKey],
clmns.is_identity AS [Identity],
CAST(ISNULL(ic.seed_value,0) AS bigint) AS [IdentitySeed],
CAST(ISNULL(ic.increment_value,0) AS bigint) AS [IdentityIncrement]