Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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?
0
VIVEKANANDHAN_PERIASAMY
Asked:
VIVEKANANDHAN_PERIASAMY
1 Solution
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now