Answer Posted / 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

What are the system database in sql server 2008?

591

---

what kind of lan types do you know? : Sql server database administration

528

---

How to test values returned by a subquery with the in operator?

686

---

What is store procedure? How do they work?

593

---

Explain the purpose of indexes?

552

---

---

what is a correlated sub-query? : Sql server database administration

490

---

What are views used for?

626

---

Explain log shipping and mention its advantages.

608

---

how do you determine the Load performance of any query in sql server {example how do u determine performance of a select stmnt which returns Dynamically many no of records ... some times 100,1000,10000 etc., }

1866

---

How are the unique and primary key constraints different?

575

---

what are the disadvantages of cursors? : Sql server database administration

504

---

Will count(column) include columns with null values in its count?

679

---

Do you know what is sql service broker?

546

---

Can we update data in a view?

607

---

Do you know what is a linked server in sql server?

559

---