SQL 检查存储过程是否正在运行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25846306/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if stored procedure is running
提问by Stefan Rogin
How to check if a stored procedure or query is still running in SQL Server?
如何检查存储过程或查询是否仍在 SQL Server 中运行？
Ideas
想法
I've thought of having a log where to write when the procedure starts and delete when it ends.
Flaws:
- it leaves open the case when the server restarts or some kind of failure inside the procedure.
- this method needs some work to be done before running the procedure, so it can't be applied on already running procedures.
Use process monitor
我想过有一个日志，在程序开始时写在哪里，在程序结束时删除。
缺陷：
- 当服务器重新启动或过程中出现某种故障时，它会保持打开状态。
- 此方法在运行程序之前需要做一些工作，因此它不能应用于已经运行的程序。
使用进程监视器
I would prefer a solution that can be incorporated as a stored procedure with procedure_nameand/or pid, parametersas input, so tracing programs or solutions using the SQL Server interface won't work.
我希望能有一个可以合并为一个存储过程的解决方案procedure_name和/或pid，parameters作为输入，所以使用SQL Server的界面将无法正常工作跟踪程序或解决方案。
Update #1
更新 #1
Usage example:
用法示例：
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'
the procedure should be called like
该过程应该被称为
test_if_running 'dbo.sp_sleeping_beauty '00:00:20''
and return true while running (for 20 seconds) and false after or if the function fails or the system is restarted
并在运行时（20 秒）返回 true，如果函数失败或系统重新启动，则返回 false
采纳答案by bummi
You might query sys.dm_exec_requestswhich will provide sesion_ID, waittime and futher rows of interest and CROSS APPLY sys.dm_exec_sql_textfiltering your query with the SQL for your procedure.
您可能会查询 sys.dm_exec_requests哪些将提供 sesion_ID、waittime 和其他感兴趣的行，并且 CROSS APPLYsys.dm_exec_sql_text使用您的过程的 SQL 过滤您的查询。
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%'
回答by mattavatar
Update:The answer given by John Clayton references the outdated SQL Server 2000 system table(sys.sysprocesses). The updated SQL is:
更新：John Clayton 给出的答案引用了过时的 SQL Server 2000 系统表(sys.sysprocesses)。更新后的 SQL 是：
SELECT
object_name(st.objectid) as ProcName
FROM
sys.dm_exec_connections as qs
CROSS APPLY sys.dm_exec_sql_text(qs.most_recent_sql_handle) st
WHERE
object_name(st.objectid) is not null
The SQL code above returns a list of names of your running processes. Note that you will need permission to view the Server/Database state.
上面的 SQL 代码返回正在运行的进程的名称列表。请注意，您将需要查看服务器/数据库状态的权限。
回答by Navneet
Use this :
用这个 ：
exec sp_who2
It returns all db activities.
它返回所有数据库活动。
you will check from this proc if your procedure currently running or not.
您将从这个 proc 检查您的程序当前是否正在运行。
Also will try that :
也将尝试：
SELECT creation_time , object_name(st.objectid) as ProcName
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
回答by John Clayton
I have been trying to figure out how to do get the list of running procedures and came across this thread. After a bit of research on MSDNI was able to figure out the following query that will provide the list of running processes:
我一直在试图弄清楚如何获取正在运行的程序列表并遇到了这个线程。在对MSDN进行了一些研究后，我能够找出以下将提供正在运行的进程列表的查询：
select
object_name(st.objectid) as ProcName
from
sys.sysprocesses as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where
object_name(st.objectid) is not null
A little bit more work will be needed to get the parameters.
需要做更多的工作来获取参数。
回答by HMan06
Old thread but you can do this,
旧线程，但你可以这样做，
SELECT @object = object_id
FROM SYS.OBJECTS
WHERE NAME = [SP NAME]
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 objectid = @object
