March 11, 2009 at 8:34 am
how can I find how long the process is being blocked in SQL server?
March 11, 2009 at 9:28 am
Activity Monitor
March 11, 2009 at 9:33 am
thanks...Can I get the same (time line) using any SQL query?
March 11, 2009 at 11:26 am
In SQL 2005 I recommend using SQL Server Profiler.
Create a new trace and select following event:
Errors and Warnings
-> Blocked Process Report
Make sure "Show All Columns" is selected.
This gives you very nice detailed XMl output of blocking processes. You can import this into SQL Table later and run queries against it if you wish. Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 12, 2009 at 9:22 am
right click on server property --> Reports --> Standard Reports -->
All Blocking Transaction
March 16, 2009 at 12:11 pm
To get the info in a query, try using this dynamic mgmt view: sys.dm_exec_requests.
For instance, to get all the spid's that are currently blocked:
SELECT *
FROM sys.dm_exec_requests
WHERE wait_time > 0
AND blocking_session_id != 0
July 12, 2010 at 6:10 am
Hi
can we use the stored procedure sp_who2 by viewing the column 'blkby' to find the blocking issues?
Koteswar
July 13, 2010 at 12:50 pm
on sqlblog.com Adam Machanic has an INCREDIBLE replacement for sp_who2 called sp_whoisactive. Get it, learn it, use it!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 15, 2011 at 2:15 pm
In our Production server, we have created a SQL Server Agent job which runs every 1 minute and monitors if there is any blocking for more than 10 seconds. In case it finds a blocking process, it send an email to us (DBAs) and we look into the issue in more details.
Below is a sample code for this job
SELECT * (here please include the information you want)
FROM sys.dm_exec_requests DR
INNER JOIN sys.dm_exec_sessions DS ON DR.Session_id = DS.Session_id
CROSS APPLY sys.dm_exec_sql_text(dr.sql_handle) AS s2
WHERE DR.Session_ID in
(SELECT blocking_session_id
FROM sys.dm_exec_requests
WHERE (wait_time/10000) > @maxtime)
Also, if you want to dig more deep like the table name, index name, type of lock etc, you may use the below query to get lock info
SELECT request_session_id as spid,
db_name(resource_database_id) as dbname,
CASE
WHEN resource_type = 'OBJECT' THEN
object_name(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN 'n/a'
ELSE object_name(p.object_id)
END as entity_name, p.index_id,i.name IndexName,
resource_type as resource,
resource_description as description,
request_mode as mode, request_status as status
FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p
ON p.hobt_id = t.resource_associated_entity_id
left join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
WHERE resource_database_id = db_id();
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply