检查存储过程是否正在运行 [英] Check if stored procedure is running
问题描述
如何检查存储过程或查询是否仍在 SQL Server 中运行?
How to check if a stored procedure or query is still running in SQL Server?
想法
我想过有一个日志,在程序开始时写在哪里,在程序结束时删除.
I've thought of having a log where to write when the procedure starts and delete when it ends.
缺陷:
- 当服务器重新启动或过程中出现某种故障时,它会保持打开状态.
- 此方法需要在运行程序之前完成一些工作,因此不能应用于已经运行的程序.
使用进程监视器
我更喜欢可以作为存储过程合并的解决方案,procedure_name
和/或 pid
、parameters
作为输入,因此跟踪使用 SQL Server 界面的程序或解决方案将不起作用.
I would prefer a solution that can be incorporated as a stored procedure with procedure_name
and/or pid
, parameters
as input, so tracing programs or solutions using the SQL Server interface won't work.
使用示例:
CREATE PROCEDURE dbo.sp_sleeping_beauty
@time_str varchar(50)
AS
SET NOCOUNT ON;
WAITFOR DELAY @time_str;
GO
dbo.sp_sleeping_beauty '00:00:10'
dbo.sp_sleeping_beauty '00:00:20'
dbo.sp_sleeping_beauty '00:00:30'
该过程应该像
test_if_running 'dbo.sp_sleeping_beauty '00:00:20''
并在运行时(持续 20 秒)返回 true,如果函数失败或系统重新启动,则返回 false
and return true while running (for 20 seconds) and false after or if the function fails or the system is restarted
推荐答案
您可以查询 sys.dm_exec_requests
这将提供 sesion_ID、waittime 和其他感兴趣的行以及 CROSS APPLY sys.dm_exec_sql_text
使用您的过程的 SQL 过滤您的查询.
You might query sys.dm_exec_requests
which will provide sesion_ID, waittime and futher rows of interest and CROSS APPLY sys.dm_exec_sql_text
filtering your query with the SQL for your procedure.
Select * from
(
SELECT * FROM sys.dm_exec_requests
where sql_handle is not null
) a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) t
where t.text like 'CREATE PROCEDURE dbo.sp_sleeping_beauty%'
这篇关于检查存储过程是否正在运行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!