CODEWITHSUNDEEP
sql-server
Fire Hand
Fire Hand

Reputation: 26396

Check if a stored proc is running?

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

Upvotes: 3

Views: 20807

Answers (5)

clowwater
clowwater

Reputation:

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

Upvotes: 0

Jon
Jon

Reputation: 2085

I asked this once :)

Check out:

Sql Server 2000 - How can I find out what stored procedures are running currently?

Upvotes: 1

aasim abdullah
aasim abdullah

Reputation: 

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/

Upvotes: 0

MartW
MartW

Reputation: 12538

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

Upvotes: 0

Robin Day
Robin Day

Reputation: 102548

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.

Upvotes: 0

Related Questions