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.

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

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

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

In my application I have a process which picks the scanned files (tif format) from a shared location and it links to application and shown on it.The actuall issue is that my process picks the file before it is completly written or scanned which results in displaying few parts of the image or incomplete image.I need to check if the file is not completly scanned or written then do not link it to application.Please help if any body tell me that how can i check that file is in written phase or locked through DTS.thanking you in advance

0 Answers  

---

What is meant by datasource?

0 Answers  

---

what command is used to create a table by copying the structure of another table?

7 Answers  

---

What is tempdb database? : SQL Server Architecture

0 Answers  

---

What is the log shipping?

0 Answers  

---

What is difference between getdate and sysdatetime in sql server 2008?

0 Answers  

---

What are the pros and cons of creating extended stored procedures?

1 Answers  

---

What is difference between process and thread? Explain lazy writer funcationality.

2 Answers   Microsoft,

---

How to change the password of a login name in ms sql server?

0 Answers  

---

1 01 101 01010

0 Answers  

---

how will add additional conditions in sql?

0 Answers   Amdocs, Apps Associates,

---

What is database replication? What are the different types of replication you can set up in sql server?

0 Answers  

---