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 |
Explain the steps needed to create a scheduled job?
When a primary key constraint is included in a table, what other constraints does this imply?
Where the sql logs gets stored? : sql server database administration
What is the difference between dataadapter and datareader?
How do I view a procedure in sql server?
What is sql service broker?
Tell me what is the difference between locking and multi-versioning?
What happens to a statement batch if there is a compilation error?
How can change procedure in sql server?
what is meant by deafult in sql server?
How to assign new column names in a view?
How to filter records of table in SQL SERVER?
0 Answers Petranics Solutions,