August 3, 2004 at 12:45 pm
Anyway to automatically detect a blocking query and kill it? Remedy Help Desk allows free form queries i.e. users doing select... where text field like %what ever%. leads to table lock, blocking, e-page in the middle of the night on a regular basis.
August 4, 2004 at 12:16 am
Can you have them using a view ?
if yes, provide a view like this :
Create myschema.myview
as
select mycol1,..
from myschema.mytable (NOLOCK)
This way they will not lock the rows they touch, but only put a schema-lock at the used objects. (only indication they use it. This will only block if someone tries to alter the objects at that time)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 4, 2004 at 10:58 am
We also use Remedy Help Desk but the tables aren't large enough yet to cause any kind of locking problem. However, you may be able to add the NOLOCK hint to the view that the application already uses (ours is version 4.0.3 and the view is called hpd_helpdesk). You may want to check with the vendor first though.
Linda
August 4, 2004 at 11:47 am
I have the same troubles with Remedy. Changing the views doesn't really help a whole bunch, and as we have a lot of large tables we get occassional blocking issues. I use the following script within a job to monitor for blocking processes. As run the script will email notification about blocking spids, which you can then login to the server and kill them, there is also a commented line which will kill the offending spid as a part of the script execution.
declare @blocker varchar(75)
declare blocker_cursor cursor for select distinct spid from master..sysprocesses where blocked <> 0
open blocker_cursor
fetch next from blocker_cursor into @blocker
while (@@fetch_status <> -1)
begin
if (@@fetch_status = -2)
begin
fetch next from blocker_cursor into @blocker
continue
end
if @blocker not in (select spid from master..sysprocesses where blocked = 0)
-- AND (select datediff(ss,last_batch,getdate()) from sysprocesses where blocked = @blocker) > 120
begin
declare @server varchar(128), @blkspid varchar(75), @badlogin varchar(75)
set @server = @@servername
select @blkspid = blocked from master..sysprocesses where spid = @blocker
print 'Blocked SPID: ' + @blocker + ' Blocking Spid: '+ @blkspid
exec ('dbcc inputbuffer (' + @blkspid + ')')
-- uncomment next line to turn on "killing" of blocking processes
-- exec ('kill ' + @blocker )
create table #temp (eventtype varchar(100), parameters varchar(100), eventinfo varchar(1024))
create table #temp2 (spid int, status varchar(256), login varchar(256), hostname varchar(256), blkby int, dbname varchar(256), command varchar(256), cputime bigint, diskio bigint, lastbatch varchar(25), programname varchar(256), spid2 int)
declare @sql varchar(100), @buffer varchar(1024)
set @sql = 'dbcc inputbuffer('+@blkspid+')'
insert into #temp exec (@sql)
select @buffer = eventinfo from #temp
set @sql = 'sp_who2 ' + @blkspid
insert into #temp2 exec (@sql)
select @badlogin = login from #temp2
drop table #temp
drop table #temp2
exec master..xp_sendmail @recipient = 'the recipient list', @subject = 'Blocking condition detected',
@message = 'ServerName: SPID ' + @blocker+' Blocking Spid: '+@blkspid+'
Login: '+ @badlogin, 'Automated Email')
RAISERROR (50001, 16, 1, @server, @blocker)
fetch next from blocker_cursor into @blocker
waitfor delay '00:00:05'
select blocked as "Blocking SPID",
count(*) as "# Blocked SPIDs"
from master..sysprocesses
where blocked > 0
group by blocked
end
else
fetch next from blocker_cursor into @blocker
end
deallocate blocker_cursor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply