SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Locks - Blocked Processes


Locks - Blocked Processes

Author
Message
Mani-584606
Mani-584606
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4318 Visits: 1998
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
luckysql.kinda
luckysql.kinda
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2836 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
ChiragNS
ChiragNS
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10205 Visits: 1865
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"
Mani-584606
Mani-584606
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4318 Visits: 1998
-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?
ChiragNS
ChiragNS
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10205 Visits: 1865
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"
Suresh B.
Suresh B.
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8294 Visits: 5330
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.
SQLQuest29
SQLQuest29
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3338 Visits: 4398
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 :-)
Scott Coleman
Scott Coleman
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10605 Visits: 1561
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.



myheadhurts
myheadhurts
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 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
andersonrj18
andersonrj18
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 87
The view, sys.sysprocesses will help you in all what you need.

example: select * from sys.sysprocesses where blocked =1
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search