Solved

SQL blocking

Posted on 2012-04-10
4
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 15

Accepted Solution

by:
Deepak Chauhan earned 500 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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

752 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