SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Blocking and Locking


Blocking and Locking

Author
Message
Glenn Stein
Glenn Stein
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 39
In Brad's recent editorial he said:

I demonstrated to the DBA how to use Management Studio to identify blocking locks, including examining the offending code. I also showed him how he could kill the blocking connection without the need for anything as drastic as rebooting the server.

Can anyone point me to information that would show me how to do this?

Thanks.
alen teplitsky
alen teplitsky
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3626 Visits: 4674
we have a script that uses sysprocesses to look for blocking spids. runs every 2 minutes. on a few servers i want to rewrite it to use the requests dmv.

in our case it runs, dumps the data into a table and sends an email alert if there is blocking. in some cases we kill a spid automatically.
Glenn Stein
Glenn Stein
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 39
Would you be willing to send me a copy of your script? You can send it to me at glenns@netvision.net.il

Thanks!
Animal Magic
Animal Magic
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2040 Visits: 13737
I use this following code to find any long running spids and kill them off. you could easily tweak it to email you about the spids rather than kill them. I do need to update it to use the DMV's for 2005 servers but have not had time, and it uses a cursor when its not neccesarily needed, but use what you want, and enhance what you want

/* =======================================================================================
CHANGE HISTORY
Version Date Author Change Detail
1.1 19/12/07 JM Because new connections from Crystal opened with a last batch of 1900-01-01 00:00
I am taking these out so we dont kill them

========================================================================================*/

use tempdb
set nocount on
create table #sp_who (
spid smallint,
ecid smallint,
status nchar(30),
loginame nchar(128),
hostname nchar(128),
blk char(5),
dbname nchar(128),
cmd nchar(16))
insert into #sp_who execute sp_who
--select * from #sp_who where cmd = 'AWAITING COMMAND' and spid > 50
--drop table #sp_who

create table [tempdb].[dbo].[logged_in_users]
(
spid smallint,
login_name [varchar] (50),
DBName [varchar] (50),
LoginTime [datetime],
Lastbatch [datetime]
)

--drop table [tempdb].[dbo].[logged_in_users]

INSERT INTO [tempdb].[dbo].[logged_in_users]
(
[spid],
[Login_Name],
[DBName],
[LoginTime],
[Lastbatch])

SELECT master.dbo.sysprocesses.spid, master.dbo.sysprocesses.loginame,
master.dbo.sysdatabases.name,
master.dbo.sysprocesses.login_time,
master.dbo.sysprocesses.last_batch
FROM master.dbo.sysprocesses INNER JOIN
master.dbo.sysdatabases ON master.dbo.sysprocesses.dbid = master.dbo.sysdatabases.dbid
where master.dbo.sysprocesses.spid > 50


--select * from [tempdb].[dbo].[logged_in_users]

/*Leave this bit in for testing!
select * from [tempdb].[dbo].[logged_in_users] LI inner join #sp_who SP on SP.Spid = LI.Spid
where SP.cmd <> 'AWAITING COMMAND' and SP.spid > 50 and LI.DBName <> 'CE10'
--Then decide how long you is too long!
and lastbatch < dateadd(hh,-0,getdate())
order by lastbatch
*/
create table #Loop
(
id smallint identity(1,1),
spid smallint
)
insert into #Loop (spid)
select LI.spid from [tempdb].[dbo].[logged_in_users] LI inner join #sp_who SP on SP.Spid = LI.Spid
where SP.cmd <> 'AWAITING COMMAND' and SP.spid > 50 and LI.DBName <> 'CE10'
--Then decide how long you think is too long!
and lastbatch (select @@SPID)
--v1.1
and lastbatch > '02 jan 1900'
--v1.1End

select * From #Loop

declare @counter smallint
declare @max smallint
declare @kill smallint
set @counter = 1
set @max = (select max(id) from #Loop)

while @counter <= @max
begin
set @kill = (Select spid from #Loop where id = @counter)
print @kill
EXEC ('KILL '+ @kill)
set @counter = @counter + 1
end

--drop tables
drop table [tempdb].[dbo].[logged_in_users]
drop table #sp_who
drop table #Loop

--sp_who2 180

--dbcc inputbuffer (180)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: Administrators
Points: 81959 Visits: 19217
There are scripts in the script library here as well that might help. Search blocker or root blocker.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
MannySingh
MannySingh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1575 Visits: 787
Best option, there is a script sp_blocker_pss80 from MS.
There there is a neat Tool called sherlock, which can be used to read for blocking scenarios, this too from MS PSS guys.
http://support.microsoft.com/kb/271509

Maninder
www.dbanation.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search