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

Where actually sql azure database is hosted?

163

---

What is a system database and what is a user database?

1085

---

What is a data collection table?

1004

---

Explain what are commit and rollback in sql?

928

---

What is the purpose of sql profiler in sql server? : sql server database administration

1103

---

What is query parameter in ssrs?

160

---

What are different types of roles provided by ssrs?

150

---

What is ms sql server index?

1098

---

How can windows applications connect to sql servers via odbc?

1120

---

How to use user defined functions in expressions?

1082

---

What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

1192

---

What is field in algebra?

1032

---

Explain the difference between primary keys and foreign keys?

1019

---

Explain sub-query?

946

---

What do you mean by subquery?

1083

---