Solved

SQL blocking

Posted on 2012-04-10
4
270 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:sqlservr
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:
deepakChauhan 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now