how to know Who Is Blocking Your SQL Server?
Answers were Sorted based on User's Feedback
Answer / mobin sathupally
Simply execute the sp_who or sp_who2 stored procedure.
In sp_who result set observe the blk column.In sp_who2
result set observe the blkby column.In both result sets we
got spids who blocking the server.
Is This Answer Correct ? | 6 Yes | 0 No |
Answer / xavier k.
Solution #1
Simply execute the sp_who2 active and check out the blocked
by column to check the blocked spid and blocked by spid
Solution #2
select * from sys.sysprocesses where blocked <>0
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / pandians
In SQL Server 2005, 2008, 2008 R2,...
Select Session_ID, Blocking_Session_ID From
Sys.Dm_Os_Waiting_Tasks Where Blocking_Session_ID Is Not
Null
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / guest
If you have ever monitored any blocking problems in SQL
Server, you know that sp_who only shows you the spid (SQL
Server's internal Process ID) that is causing the blocking
for each spid that is blocked. Often a blocked spid is shown
as causing blocking for another spid. To see the spid (or
spids) that started the whole mess off, execute the
following SQL:
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM master..sysprocesses p
JOIN master..sysdatabases d ON p.dbid = d.dbid
JOIN master..syslogins l ON p.suid = l.suid
WHERE p.blocked = 0
AND EXISTS ( SELECT 1
FROM master..sysprocesses p2
WHERE p2.blocked = p.spid )
We built this into our own version of sp_who, called
sp_hywho. See the listing below. Code for sp_hywho:
if exists (select * from sysobjects
where id = object_id('dbo.sp_hywho')
and sysstat & 0xf = 4)
drop procedure dbo.sp_hywho
GO
Create Procedure sp_hywho
( @vcDBName sysname = NULL )
AS
SET NOCOUNT ON
IF EXISTS ( SELECT 1
FROM master..sysprocesses p
WHERE p.blocked = 0
AND EXISTS ( SELECT 1
FROM master..sysprocesses p2
WHERE p2.blocked = p.spid ) )
BEGIN
PRINT "Blocking caused by:"
PRINT ""
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM master..sysprocesses p
JOIN master..sysdatabases d ON p.dbid = d.dbid
JOIN master..syslogins l ON p.suid = l.suid
WHERE p.blocked = 0
AND EXISTS ( SELECT 1
FROM master..sysprocesses p2
WHERE p2.blocked = p.spid )
AND (p.dbid = DB_ID( @vcDBName ) OR @vcDBName IS NULL)
ORDER BY 2,IsNull(Ltrim(program_name),"ZZZZZZZZZ"),4,5
PRINT ""
END
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM master..sysprocesses p
JOIN master..sysdatabases d ON p.dbid = d.dbid
JOIN master..syslogins l ON p.suid = l.suid
WHERE (p.dbid = DB_ID( @vcDBName ) OR @vcDBName IS NULL)
ORDER BY 2,IsNull(Ltrim(program_name),"ZZZZZZZZZ"),4,5
SET NOCOUNT OFF
GO
if exists (select * from sysobjects
where id = object_id('dbo.sp_hywho')
and sysstat & 0xf = 4)
GRANT EXEC ON dbo.sp_hywho TO PUBLIC
GO
Is This Answer Correct ? | 1 Yes | 1 No |
What is exclusive locks?
how to connect sybase to sql server 2005?.
How to generate create view script on an existing view?
What is a user-defined function in the sql server and what is its advantage?
which one will take 1st priority in case of insert statement and select statement???
Can sql servers link to other servers?
What is the difference between coalesce() & isnull()?
Which system table contains information on constraints on all the tables created?
What is transaction server distributed transaction?
How many full-text indexes can a table have?
Do you know what are the reporting services components?
How to find the login name linked to a given user name?