Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Blocked and Blocking process Help


Blocked and Blocking process Help

Author
Message
pavargasq
pavargasq
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11054 Visits: 14859
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Tsinelas
Tsinelas
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 221
You can use the "sp_who2 Active" you can find there what is blocked spid and block by spid.


Cheers!

dev1.bohol
@SQLFRNDZ
@SQLFRNDZ
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 1177
use sp_who2


--SQLFRNDZ

ponting912
ponting912
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: 14
sp who2?? what is it i didn't understand??

Check it out and get tubing tubes
Tsinelas
Tsinelas
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 221
'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
ponting912
ponting912
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: 14
Oh i see thanks now i understand what it is.. Thankyou:-)

Check it out and get tubing tubes
Tsinelas
Tsinelas
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 221
You are welcome my friend Smile


Cheers!

-dev1.bohol@gmail.com
thadeushuck
thadeushuck
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 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
Tsinelas
Tsinelas
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 221
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
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