Deadlocks in SQL2008R2 due to DDL triggers

· Uncategorized
Authors

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]
on database
AFTER DDL_DATABASE_LEVEL_EVENTS
as

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: