August 22, 2008 at 4:26 am
Hi,
Processes are locked for an excessive time.how can we see that which blocked processes are blocking . I found this script from google but it doesnt work plz tell me whats wrong is this its shows me error
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 )
Error is :-
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'suid'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'suid'.
Advise me
Regards
Jagpal singh
August 22, 2008 at 4:58 am
Hello,
The column "suid" does not exist in those system views/tables. Presumably the author meant "sid"?
Does sp_who2 not give you the information that you need?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 22, 2008 at 5:13 am
Thanxx ,
John for ur reply but i just want to see that which processes are blocked from last 20 minutes I have another query as well but i am not confirmed that this query giving me right answer.
select *from master ..sysprocesses where waittime >12000
Pls Advise
Regards
Jagpal singh
August 22, 2008 at 5:35 am
Hello,
Do you want to see processes that are blocked by another processes (in which case an amended version of the original query would work; as would sp_who2), or do you want to see what processes have been running for more than 20 minutes (in which case the second query is more useful).
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply