Blocking and Locking

Question

Blocking and Locking

Glenn Stein

Newbie

Points: 6
More actions
June 26, 2008 at 12:32 am

#122117

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

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 Newbie Points: 6 More actions · Answer 2

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 SSChampion Points: 13964 More actions · Answer 3

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

VersionDateAuthorChange Detail

1.119/12/07JMBecause 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 (

spidsmallint,

ecidsmallint,

statusnchar(30),

loginamenchar(128),

hostnamenchar(128),

blkchar(5),

dbnamenchar(128),

cmdnchar(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-2 smallint

declare @kill smallint

set @counter = 1

set @max-2 = (select max(id) from #Loop)

while @counter <= @max-2

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 - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

There are scripts in the script library here as well that might help. Search blocker or root blocker.

MannySingh SSChampion Points: 12465 More actions · Answer 5

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