Hi Pete,

I used the following script running every minute to log what processes
is causing the blocks, and log them in a table so we can identify the
problems. A minor tweak should do what you want.

==============================


declare @SPID varchar(5)
declare @stmt nvarchar(64)
--Print 'Head of the blocking chain'

select x.spid as spid INTO #Blocks from master..sysprocesses x where
x.blocked = 0 and x.spid in (select y.blocked from master..sysprocesses
y where y.blocked!=0 )


select * from #Blocks

declare SPIDCursor cursor

for select SPID from #Blocks



open SPIDCursor


Fetch next from SPIDCursor
into @SPID



WHILE @@FETCH_STATUS = 0
begin

set @stmt = 'DBCC InputBuffer(' + @SPID + ')'

insert into utbl_BlockingQueries
(EventType, Parameters, EventInfo)
exec sp_executesql @stmt

Fetch next from SPIDCursor
into @SPID

end



drop table #Blocks

close SPIDCursor
deallocate SPIDCursor

=====================

-----Original Message-----
From: Peter
[mailto:mssqldba-ezmlmshield-x28371055.[Email address protected]
Sent: 31 January 2007 12:46
To: LazyDBA Discussion
Subject: Kill spids automatically


Good morning everyone,

I'm trying to formulate a script that will 'automatically' kill this
process that blocks other users from running commands against the
database.
Of course, this will be a temporary fix, until we redesign the database
structure.

Any ideas or suggestions that you may have will be much appreciated.

Thank you,

Pete.:)

Peter M. Florenzano
Database Administrator
Distributed Computing
Email: Peter.[Email address protected]
Phone: (203)752-4317



CONFIDENTIALITY NOTICE: This message and any attachments may contain
confidential, proprietary or legally privileged information and is
intended only for the use of the addressee or addressees named above for
its intended purpose. If you are not the intended recipient of this
message, this message constitutes notice that any review,
retransmission, distribution, copying or other use or taking any action
in reliance on the information in this message and its attachments, is
prohibited. If you receive this communication in error, please
immediately advise the sender by reply e-mail and delete this message
and its attachments from your system without keeping a copy. Unless
expressly stated in this e-mail, nothing in this message may be
construed as a digital or electronic signature. Thank you.


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Click
https://www.mailcontrol.com/sr/wQw0zmjPoHdJTZGyOCrrhg==
IbyahSH!nUp9b62CngIAdkgwr!GEAB57U9wycoQQn+wrkbR1lzvydxpXImUFiHIYg49kPvkT
YyqmPU80P9EeSltWtZXbZSJsY2nBTy2cK1w6fFs2fhvw7P3JsmaZvoC4rrZZ3GsHIlazvdUc
KdSSHRci89iBP8bV+Oac++HNCHt2k9xutjQbw11 to report this email as spam.

MS Sql Server LazyDBA home page