sp_lock, DBCC InputBuffer and ADO.NET
-
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
-
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.
-
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
-
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