I am experiencing sporadic deadlock behaviour when my application tries to execute any DROP TABLE statement against my database. I tried capturing the deadlock graph and this is the output from deadlock xml

      <victimProcess id="process2bddc28" />
      <process id="process2bddc28" taskpriority="0" logused="0" waitresource="KEY: 6:281474978938880 (85262e079cd2)" waittime="1900" ownerId="76493296" transactionname="user_transaction" lasttranstarted="2016-02-22T05:58:15.867" XDES="0x621cb38e0" lockMode="S" schedulerid="6" kpid="10748" status="suspended" spid="119" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2016-02-22T05:58:16.080" lastbatchcompleted="2016-02-22T05:58:16.080" lastattention="1900-01-01T00:00:00.080" hostname="DEV01" hostpid="13420" isolationlevel="read committed (2)" xactid="76493296" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
        <frame procname="mssqlsystemresource.sys.sp_columns_100" line="138" stmtstart="12020" stmtend="17532" sqlhandle="0x0300ff7f78a964e13656e600e7a5000001000000000000000000000000000000000000000000000000000000">
       <inputbuf>Proc [Database Id = 32767 Object Id = -513496712]</inputbuf>
      <process id="process45c8ca8" taskpriority="0" logused="6748" waitresource="KEY: 6:562949956108288 (5db581fe7a0b)" waittime="1900" ownerId="76493663" transactionname="user_transaction" lasttranstarted="2016-02-22T05:58:16.087" XDES="0x16aeded90" lockMode="X" schedulerid="1" kpid="18268" status="suspended" spid="129" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-02-22T05:58:16.090" lastbatchcompleted="2016-02-22T05:58:16.090" lastattention="1900-01-01T00:00:00.090" hostname="DEV01" hostpid="20128"  isolationlevel="read committed (2)" xactid="76493663" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="128056">
        <frame procname="adhoc" line="1" sqlhandle="0x01000600702bfd0bd083047f0600000000000000000000000000000000000000000000000000000000000000">
    DROP TABLE "dbo"."USER_TABLE_1"    </frame>
    DROP TABLE "dbo"."USER_TABLE_1"   </inputbuf>
      <keylock hobtid="281474978938880" dbid="6" objectname="DEV_01.sys.sysschobjs" indexname="clst" id="lock35af27b80" mode="X" associatedObjectId="281474978938880">
       <owner-list><owner id="process45c8ca8" mode="X" /></owner-list>          
       <waiter-list><waiter id="process2bddc28" mode="S" requestType="wait" /></waiter-list>     
      <keylock hobtid="562949956108288" dbid="6" objectname="DEV_01.sys.syscolpars" indexname="nc" id="lock453c34600" mode="U" associatedObjectId="562949956108288">
       <owner-list><owner id="process2bddc28" mode="S" /></owner-list>
       <waiter-list><waiter id="process45c8ca8" mode="X" requestType="convert" /></waiter-list>

This behaviour was not seen in earlier versions of SQL Server. Any thoughts on this?

  • 1
    That sp_columns_100 might be used to return table column information for ODBC drivers. I wonder if you're getting so many active ODBC connections, all requesting column information, that it's locking out any schema changes. – Cody Konior Apr 28 '16 at 0:45
  • But why doesn't this happen in Sql Server 2012? – vkr Apr 28 '16 at 0:59
  • I don't have the answer. Just adding an idea for others in case it helps them come up with the answer. – Cody Konior Apr 28 '16 at 4:26
  • When was the last time you ran checkdb? How was this database migrated to 2014? What version did it come from? The resources involved are internal tables which makes me believe something to be amiss with the physical/logical metadata OR there is something else running against this database we don't know about. – Sean says Remove Sara Chipps Apr 28 '16 at 5:48
  • 2
    Have you tried using Adam Machanic's sp_whoisactive to see what's going on while you're dropping? This will tell you who's really holding onto the table you're trying to drop. Use @find_block_leaders=1 and @sort_order='[blocked_session_count]DESC' to make the output easier to visualize – Craig Efrein Apr 28 '16 at 7:55

Just my 5 cents contribution to the topic (not a complete answer)

I had a similar issue and I've caught the blocking query with sp_whoisactive - so it is

create procedure sys.sp_columns_100
    @table_name         nvarchar(384),
    @table_owner        nvarchar(384) = null,
    @table_qualifier    sysname = null,
    @column_name        nvarchar(384) = null,
    @NameScope          int = 0,
    @ODBCVer            int = 2,
    @fUsePattern        bit = 1 -- To allow users to explicitly disable all pattern matching.

and as @Cody_Konior suggested I do have "many active ODBC connections" from legacy SSIS packages in my ETL system, so...

perhaps it's time to finally change them to OLEDB or ADO.NET versions as I wanted for some time already but posponed

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.