Check if a stored proc is running?

Question

Is it possible to check is there any stored proc currently running in SQL Server???

Answer 1

There's the activity monitor in management studio. There's also sp_who and sp_who2. These will give you an idea of what is running.

However, if you need to programatically find out if a procedure is "in progress" or not to avoid calling it again then I'd consider a flag somewhere to indicate "SPIsRunning" that you set at the start and end of the procedure itself.

Answer 2

Via TSQL you could try evaluating the results of DBCC INPUTBUFFER for each SPID, but it's pretty cumbersome to do.

Answer 3

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 4

I asked this once :)

Check out:

http://stackoverflow.com/questions/129086/sql-server-2000-how-can-i-find-out-what-stored-procedures-are-running-currently

Answer 5

If you just want to see activity use SQL Profiler tracing Stored Procedures starting & completed. Maybe that helps you.