locked
Deadlocks in SQL2008R2 due to DDL triggers. RRS feed

  • Question

  • 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.

    Is this a knwn bug in SQL2005R2? Is a bugfix planned? 

    We temporary disabled the DDL trigger and are investigation some build-in auditing options in SQL2008R2.


    Friday, June 10, 2011 8:17 AM

Answers

All replies