Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Blocking and Locking Expand / Collapse
Author
Message
Posted Thursday, June 26, 2008 12:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 8, 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
Posted Thursday, June 26, 2008 9:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
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
Posted Thursday, June 26, 2008 9:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 8, 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
Posted Thursday, June 26, 2008 9:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 999, Visits: 13,473
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
Posted Thursday, June 26, 2008 10:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:30 PM
Points: 33,055, Visits: 15,167
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
Posted Thursday, June 26, 2008 11:56 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:30 AM
Points: 646, Visits: 731
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 »

Add to briefcase

Permissions Expand / Collapse