Get Query Status

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