Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

blocking Expand / Collapse
Author
Message
Posted Wednesday, March 11, 2009 8:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 4, Visits: 383
how can I find how long the process is being blocked in SQL server?
Post #673317
Posted Wednesday, March 11, 2009 9:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 1, 2009 7:50 AM
Points: 21, Visits: 88
Activity Monitor
Post #673384
Posted Wednesday, March 11, 2009 9:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 4, Visits: 383
thanks...Can I get the same (time line) using any SQL query?
Post #673392
Posted Wednesday, March 11, 2009 11:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 11, 2014 10:53 AM
Points: 942, Visits: 1,061
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.
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.


How to ask for help .. Read Best Practices here.
Post #673539
Posted Thursday, March 12, 2009 9:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 6, 2012 10:04 AM
Points: 144, Visits: 455
right click on server property --> Reports --> Standard Reports -->

All Blocking Transaction
Post #674361
Posted Monday, March 16, 2009 12:11 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:30 PM
Points: 379, Visits: 558
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


Dave Mason
Orange County, FL
Post #676819
Posted Monday, July 12, 2010 6:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 14, 2010 6:06 AM
Points: 76, Visits: 199
Hi

can we use the stored procedure sp_who2 by viewing the column 'blkby' to find the blocking issues?

Koteswar
Post #950694
Posted Tuesday, July 13, 2010 12:50 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 4,319, Visits: 6,112
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 at GMail
Post #951746
Posted Monday, August 15, 2011 2:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 1:41 AM
Points: 4, Visits: 44
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();
Post #1160240
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse