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

Blocked and Blocking process Help Expand / Collapse
Author
Message
Posted Wednesday, October 21, 2009 3:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, December 08, 2012 8:24 PM
Points: 40, Visits: 135
Good afternoon,

I work in a production system, i'm in charge of monitoring a bunch of sqlprocess in sqlserver 200 server in order to take care of blocked or blocking processes in a level of tables, databse, resources, etc.

i simple command but useful is

sp_ active

and then type dbcc inputbuffer(id_process)

Is the any other "magic" and useful commands in sqlserver thet tells me which processes are blocking and which ones are being blocked, and which ones are clutering the server resources.

I'd apprciate your help





Post #806835
Posted Thursday, October 22, 2009 6:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 10,910, Visits: 12,547
I don't know what sp_active does since it is a custom stored procedure, but you could look at sp_lock.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #807098
Posted Monday, June 25, 2012 11:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:33 AM
Points: 25, Visits: 159
You can use the "sp_who2 Active" you can find there what is blocked spid and block by spid.


Cheers!

dev1.bohol
Post #1320790
Posted Friday, June 29, 2012 4:55 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:04 PM
Points: 450, Visits: 991
use sp_who2


--SQLFRNDZ
Post #1323361
Posted Saturday, June 30, 2012 12:18 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 16, 2013 11:21 AM
Points: 2, Visits: 12
sp who2?? what is it i didn't understand??

Check it out and get tubing tubes
Post #1323472
Posted Sunday, July 01, 2012 7:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:33 AM
Points: 25, Visits: 159
'sp_who2' It is a stored procedure which is installed with SQL Server. When you execute it, you may able to see the results of blocking processes, SPID's users, hostname, command, status and etc...

Each row contains a number of useful columns like what i mentioned above. you can also view what its resource usage is in terms of CPU/IO and what login is currently executing the command.


try to execute this:

sp_who2 Active & sp_who2

Cheers!

-dev1.bohol
Post #1323548
Posted Sunday, July 01, 2012 7:45 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 16, 2013 11:21 AM
Points: 2, Visits: 12
Oh i see thanks now i understand what it is.. Thankyou

Check it out and get tubing tubes
Post #1323549
Posted Sunday, July 01, 2012 8:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:33 AM
Points: 25, Visits: 159
You are welcome my friend :)


Cheers!

-dev1.bohol@gmail.com
Post #1323555
Posted Monday, July 02, 2012 10:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 01, 2013 1:47 PM
Points: 167, Visits: 164
DECLARE @cmd		VARCHAR(100), 
@spid VARCHAR(4),
@SQL VARCHAR(100),
@time DATETIME,
@loginname VARCHAR(100)

if OBJECT_ID('TEMPDB..#TempConn2') is not null
DROP TABLE #TempConn2
create table #TempConn2
(EventType nvarchar(30), Parameters Int,EventInfo nvarchar(4000),SPID INT NULL,loginname VARCHAR(100)NULL)

--blocking
DECLARE my_cursor CURSOR FOR
SELECT [SPID],loginame
FROM sys.sysprocesses
where blocked<>0

OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @spid,@loginname
WHILE @@FETCH_STATUS = 0
BEGIN
begin
set @SQL = 'dbcc inputbuffer (' + @spid + ')'
insert into #TempConn2 (EventType ,Parameters,EventInfo)
execute(@SQL)
UPDATE #TempConn2 SET SPID=@spid,loginname=@loginname where SPID is NULL
end
FETCH NEXT FROM my_cursor
INTO @spid,@loginname
END
CLOSE my_cursor
DEALLOCATE my_cursor
select * from #TempConn2
--where eventinfo like '%text%'
GO

this is what i tend run for blocking
Post #1323908
Posted Monday, July 02, 2012 6:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:33 AM
Points: 25, Visits: 159
Hi,

The script T-SQL you privided is good, but you can just use the dbcc inputbuffer(SPID#) only once you have identified the blocked process and blocked by process.

By running the command i mentioned, you may able to see what particular T-SQL that is about to run or running.


Cheers!

dev1.bohol
Post #1324091
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse