Post reply

sp_lock, DBCC InputBuffer and ADO.NET

  • May 2, 2005 at 7:35 am

    #362520

    Hello Everyone,

    Since we are using the ADO.NET API it is more difficult to gather information about lock without using Enterprise Manager.

    If I use the procedure "sp_who" followed by the appropriate "dbcc inputbuffer (spid)" command in the past (before using ADO.NET), I was able to see the statement involved in a blocking lock.

    But now, since ADO.NET is wrapping the SQL statements in the procedure sp_executesql, all we can see using the "dbcc inputbuffer (spid)" command is:

    sp_executesql, 1

    If we use the sp_lock stored procedure instead, all we have is this information (an example):

    spid   dbid     ObjName        IndId  Type Resource         Mode  Status

    ----   -------  -------------  -----  ---- --------------   ----  ------

    105    166      sp_recompile   1      KEY  (31003cf2ce3d)   U     WAIT

    As we can see, the name of the object is sp_recompile and the resource is (31003cf2ce3d) .

    Is there a way, having this resource id, to get the object (table or index) involved?

    Thanks everyone,

    Carl

     

  • May 2, 2005 at 10:22 am

    #556335

    Try this query

     

    select o.name as 'Ojbect', case o.type when 'u' then 'Table' when 'v' then 'View' when 'S' then 'System Table' else '' end  from master..syslockinfo l,  sysobjects o where o.id = rsc_objid and req_spid = YOUR SPID HERE

     

     

    run it from the database you are searching the lock on.

  • May 2, 2005 at 10:35 am

    #556337

    Hello Mike,

    I will try it but knowing that sp_lock add taken its information from syslockinfo and used the rsc_objid to output the object Name and giving me sp_recompile, I am not sure it will help.

    Tanks,

    Carl

  • May 2, 2005 at 10:42 am

    #556343

    In fact, this is my error.

    I was using object_name(rsc_objid) not being in the rigth DB .

    Forget all that.

    Best regards

    Carl

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply