June 27, 2014 at 2:54 am
Hi,
Can anyone please reply with a good T SQL statement which can fetch the running commands also?
I mean get the SPID from sysprocesses and then feeding those to DBCC INPUTBUFFER to get the commands against all SPPIDs connected to the same database.
Note: The sql instance is - 2005
Thanks.
June 27, 2014 at 4:33 am
Select from sys.dm_exec_requests cross apply to sys.dm_exec_sql_text.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2014 at 4:41 am
You might also want to look up sp_whoisactive by Adam Machanic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 30, 2014 at 12:42 am
July 1, 2014 at 10:19 am
I use this ALL THE TIME. I am on 2008, and I don't have a 2005 machine on which to test this. I'm pasting it as-is with my comments at the bottom that I uncomment and run occasionally.
SELECT sp.spid,
sp.STATUS,
sp.program_name,
sp.loginame,
sp.hostname,
CONVERT(CHAR(3), sp.blocked) as 'Blk',
--sp.open_tran,
DB_NAME(sp.dbid) as 'DB',
sp.cmd,
SQLStatement =
SUBSTRING
(qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)--,
--er.sql_handle
FROM sys.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
where SUBSTRING
(qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
) <> ''
-- Can also use DBCC INPUTBUFFER(spid)
/*
-- Also interesting:
select s.session_id,
s.login_name,
s.host_name,
s.status,
s.program_name,
s.cpu_time,
s.last_request_start_time,
(SELECT text FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) AS query_text
from sys.dm_exec_sessions s
inner join sys.dm_exec_connections c on s.session_id = c.session_id
where s.session_id > 50
order by s.last_request_start_time desc
*/
-- Check an individual statement
-- Uncomment er.sql_handle in first query and then pass it to this:
--select * from sys.dm_exec_sql_text(0x02000000F0270F35A7CBFC416E4EB541969F9FC67BA7C8B4)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply