Blocking and Locking

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Blocking and Locking

Blocking and Locking

Rate Topic

Display Mode

Topic Options

Author

Message

Glenn Stein

Posted Thursday, June 26, 2008 12:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 08, 2009 1:50 PM
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.

Post #523893

alen teplitsky

Posted Thursday, June 26, 2008 9:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:15 PM
Points: 1,409, Visits: 4,509

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.

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]

Post #524311

Glenn Stein

Posted Thursday, June 26, 2008 9:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 08, 2009 1:50 PM
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!

Post #524325

Animal Magic

Posted Thursday, June 26, 2008 9:47 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:08 AM
Points: 983, Visits: 13,350

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)

Post #524327

Steve Jones - SSC Editor

Posted Thursday, June 26, 2008 10:20 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:30 PM
Points: 31,436, Visits: 13,751

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

Post #524362

MannySingh

Posted Thursday, June 26, 2008 11:56 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:10 PM
Points: 646, Visits: 729

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

Post #524468

« Prev Topic | Next Topic »

Permissions