July 16, 2009 at 7:10 pm
Experts ,
Do you have any query which just tell the blocker ,blocked , duration , and why it is blocked ,blocking and type of lock it is placing?
Thanks!!
July 16, 2009 at 10:10 pm
for a starter this query should help u.
select * from sys.sysprocesses where blocked0
July 16, 2009 at 10:48 pm
Hi,
Along with, PS correct approach, the DBCC inputbuffer shows the contents of
sp/table/.. against the SPID
ARUN SAS
July 17, 2009 at 12:15 am
Thanks , do we have combination of above two to give exact in same query?
July 17, 2009 at 12:42 am
Also use SP_WHO2 command to know more about the process that is blocking and got blocked.
July 17, 2009 at 2:27 am
Try
SELECT (SELECT name FROM sys.dm_exec_sql_text(sql_handle) a JOIN DATABASENAME.sys.all_objects b ON a.objectid = b.object_id),
(SELECT substring(text, stmt_start/2,
((case when stmt_end = -1 then
(len(convert(nvarchar(max), text)) * 2)
else
stmt_end
end) - stmt_start) / 2) FROM sys.dm_exec_sql_text(sql_handle)),
*
FROM sys.sysprocesses
WHERE (blocked 0 OR spid IN (SELECT blocked FROM sys.sysprocesses))
AND dbid = db_id('DATABASENAME')
From http://itknowledgeexchange.techtarget.com/sql-server/handy-script-for-use-when-looking-at-blocking/
July 17, 2009 at 7:32 am
Some of those views are deprecated in 2008. Get used to using DMV's for this type of data. Here's a script from Technet
select
t1.resource_type,
'database'=db_name(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address and
t1.request_session_id = isnull(@spid,t1.request_session_id)
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply