I need a way to determine if a query is still running on the database server. Is there a way to do this?
If the query is still running after 2 hours I need to send a message. How can I query the database for the status of a query?
This will give you an idea of what queries are currently running on your server, and what the query is:select percent_complete,* from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(sql_handle)
where session_id > 50
This will tell you the longest open tran in the db:
DBCC OPENTRAN|||
If you know the session ID in question, then you can use this statement posted today by Louis Davidson.
declare @.spid int
set @.spid = ?
select
der.session_id,
der.wait_type,
der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as sysname) as databaseName,
des.program_name,
der.command as commandType,
execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand,
der.open_transaction_count
from
sys.dm_exec_sessions des
inner join
sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply
sys.dm_exec_sql_text(der.sql_handle) as execText
where
des.session_id = @.spid;
Check BOL for more info about these DMVs.
AMB
