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 12»»

Locks - Blocked Processes Expand / Collapse
Author
Message
Posted Tuesday, August 11, 2009 10:51 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:47 PM
Points: 587, Visits: 1,998
Hi,
We are using Spotlight for monitoring and we are getting Blocked processes alarms. I would like to know where can I check this blocked processes in SQL server. I have checked the error log But I did not find any errors.


Spotlight Enterprise has raised an alarm:

Connection: ins1_sqlserver
Time: 10/08/09 18:28:34.042
Severity: High

Locks - Blocked Processes: 1 processes are currently waiting on locks (blocked).


thanks
Post #769103
Posted Wednesday, August 12, 2009 1:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, Visits: 659
sp_who2 is ur friend. You can use following from performance monitor:
SQLServer:Locks Lock Timeouts/sec _Total
SQLServer:Locks Number of Deadlocks/sec _Total

-LK
Post #769153
Posted Wednesday, August 12, 2009 6:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Hi

Use DBCC Traceon (1222, 3605, -1) for this. This will ouput the deadlock information to the error log. Analyzing the dead lock info so got is slightly complicated if you have not doe it before. There are some good articles in this site for the same.


"Keep Trying"
Post #769261
Posted Wednesday, August 12, 2009 2:58 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:47 PM
Points: 587, Visits: 1,998
-T1222 is on our server and I'm getting dead lock graph in error log whenever a dead lock occurs. But how to deal with Blocking?
Post #769698
Posted Wednesday, August 12, 2009 11:28 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Hi

You need to analyze the deadlock graph and see which process is causing the deadlock. You will be able to make out the stored procedure/query which is causing this. Once you get this info you can do the necessary optimizations.
I am not an expert at analyzing the deadlock graph, but there are others here who can do that. Maybe if you can post the graph somebody can help.


"Keep Trying"
Post #769876
Posted Thursday, August 13, 2009 3:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
Mani (8/12/2009)
-T1222 is on our server and I'm getting dead lock graph in error log whenever a dead lock occurs. But how to deal with Blocking?

select * from sys.dm_exec_requests
where blocking_session_id <> 0
and wait_time/1000 >= 120

Above query shows information about sessions blocked for more than 2 minutes.
Customize it according to your requrement.
Schedule a SQL Server Agent job to run once in a few minutes to email you the list of blocked processes.
If you receive email alert, deal with it.
Hope it helps you.
Post #769996
Posted Thursday, August 13, 2009 9:11 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 737, Visits: 3,777
Hi,

sp_who2 is useful for blocking. it will show which SPID is blocked by whom.

Then you can use DBCC INPUTBUFFER (SPID ID) to see what the SPID is actually doing i.e the T-SQL used by the SPID.

also, have a look at : http://technet.microsoft.com/en-us/library/cc966540.aspx

hope this helps,

\\K


______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor
Post #770243
Posted Thursday, August 13, 2009 10:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 4:29 PM
Points: 2,844, Visits: 1,155
Since you already have Spotlight running, did you try drilling down to have it show you the sessions and queries involved in the blocking? If the blocking has already cleared, you should be able to use the history feature to set the display time back to when the block occurred, and then drill down.


Post #770302
Posted Monday, August 17, 2009 12:48 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 1:41 PM
Points: 136, Visits: 635
SSMS-Management-Activity Monitor will dynamically show all processes with blocked/blocking status.

-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
Post #772179
Posted Monday, August 17, 2009 12:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 14, 2011 5:49 AM
Points: 26, Visits: 87


The view, sys.sysprocesses will help you in all what you need.

example: select * from sys.sysprocesses where blocked =1
Post #772183
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse