Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL blocking

Posted on 2012-04-10
4
Medium Priority
?
290 Views
Last Modified: 2012-04-14
I want to capture the SQL blocking when blocking is occurring for morethan 15 mins and value of block is morethan 30.
How to do the following?
0
Comment
4 Comments
 
LVL 10

Expert Comment

by:Ramesh Babu Vavilla
ID: 37831277
to monitor the blocking
use sql profiler
step  are as below

open sql profiler
select TSQL_LOcks , click on  run

al the blocking are saved in a table is define or else a trc file
0
 
LVL 25

Expert Comment

by:jogos
ID: 37831391
Don't use profiler to monitor for long period!

Best way is to make an repeating job of it to scan regularly (5 or 10 minutes) if there is a problem in that 30-minute size
Finding idle session C. Finding idle sessions that have open transactions http://msdn.microsoft.com/en-us/library/ms176013.aspx 

And combining it with Sys.dm_tran_locks http://msdn.microsoft.com/en-us/library/ms190345.aspx you can see if it's a locking problem.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 37831444
I am using following query to find out SQL blockings.

But how to trigger this query?It sould runn only if following condition exist and should save the result in the eventlogs

conditions:
locking is occurring for morethan 15 mins and value of block is morethan 30.
0
 
LVL 16

Accepted Solution

by:
Deepak Chauhan earned 2000 total points
ID: 37832178
Hi, You can use below script and schedule it in sqlagent job with time  duration in every  20 minutes

use master
go
if exists (select name from master.sys.objects where name='Blocking_log')
drop table blocking_log
begin

SELECT
             s.spid, BlockingSPID = s.blocked, waittime/60000 as Waittime, DatabaseName = DB_NAME(s.dbid),
             s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid, s.dbid), Definition = CAST(text AS VARCHAR(MAX))
 INTO        blocking_log
 FROM      sys.sysprocesses s
 CROSS APPLY sys.dm_exec_sql_text (sql_handle)
 WHERE   s.spid > 50 and waittime/60000 >=15
end
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question