We used DDL triggers in SQL 2005 for auditing purposes. When migrating to SQL2008R2, these DDL triggers generated deadlocks on system tables.
Our ETL process starts with launching multiple sub-packages in parallel each creating a number of temporary work objects (tables, views and even stored procedures (?)).
There is also a DDL trigger that captures object CUD statements and logs them into a central database.
|create trigger [ddltrig]
This worked fine in SQL2005; but generates deadlocks in SQL2008R2 on sys.sysschobjs.
|2011-06-08 09:01:04.45 spid24s resource-list
2011-06-08 09:01:04.45 spid24s keylock hobtid=281474978938880 dbid=11 objectname=ODT.sys.sysschobjs indexname=clst id=lock51eef8f00 mode=X associatedObjectId=281474978938880
Disabling the DDL trigger solved our problem.
We temporary disabled the DDL trigger and are investigation some build-in auditing options in SQL2008R2.