Check if saved proc process is working?

Question

Can I check if a stored program is currently running on SQL Server?

Answer 1

I asked about this once :)

Departure:

Sql Server 2000 - How do I know which stored procedures are currently running?

Answer 2

There is an activity monitor in the management studio. There are also sp_who and sp_who2. This will give you an idea of ​​what works.

However, if you need to programmatically find out whether the procedure is running or not, to avoid calling it again, I would look at the flag somewhere to indicate the "SPIsRunning" that you set at the beginning and end of the procedure itself.

Answer 3

Through TSQL, you can try to evaluate the DBCC INPUTBUFFER results for each SPID, but this is rather cumbersome to do.

Answer 4

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
    FROM sys.dm_exec_query_stats AS deqs
            CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    WHERE dest.TEXT LIKE '%YOUR OBJECT NAME HERE%'    
ORDER BY deqs.last_execution_time DESC

---

http://connectsql.blogspot.com/

Answer 5

If you just want to see activity, use the SQL profiler to track the start and end of stored procedures. Maybe this will help you.