SQL blocking

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?
LVL 5
VIVEKANANDHAN_PERIASAMYAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ramesh Babu VavillaCommented:
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
jogosCommented:
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
VIVEKANANDHAN_PERIASAMYAuthor Commented:
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
Deepak ChauhanSQL Server DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.