October 21, 2009 at 3:08 pm
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
October 22, 2009 at 6:50 am
I don't know what sp_active does since it is a custom stored procedure, but you could look at sp_lock.
Jack Corbett
Consultant - Straight Path Solutions
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
June 25, 2012 at 11:13 am
You can use the "sp_who2 Active" you can find there what is blocked spid and block by spid.
Cheers!
dev1.bohol
June 29, 2012 at 4:55 pm
June 30, 2012 at 12:18 pm
sp who2?? what is it i didn't understand??
Check it out and get tubing tubes
July 1, 2012 at 7:41 am
'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
July 1, 2012 at 7:45 am
Oh i see thanks now i understand what it is.. Thankyou:-)
Check it out and get tubing tubes
July 1, 2012 at 8:56 am
July 2, 2012 at 10:45 am
DECLARE @cmdVARCHAR(100),
@spidVARCHAR(4),
@sqlVARCHAR(100),
@timeDATETIME,
@loginnameVARCHAR(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
July 2, 2012 at 6:26 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply