How to detect whether a stored procedure is being run in SQL Server 2008 R2

Is there a view or internal sp to do this?

For example, I have a sp spGoesOnForSomeTime.

If I kicked this off then some individual closes my computer down, how can I se开发者_Python百科e whether this is still running or not?

I realise I can use SQL Profiler and ActivityMonitor but I ultimately want to relay this information back through a web app.

EDIT: Apologies, it is not a local connection.

---

If it's run under your local connection, then it will stop and roll back, so you can be confident that it is not running.

If it's running under the context of another connection, you can use the sp_who stored procedure to see all of the activity (and active connections) on the server, and the cmd column should provide you with the command. If any of the records have your procedure name in their cmd column, then that will tell you that it's executing.

You may, however, want to take a more intentional approach and set a flag of some kind (a value in a row in another table, an extended property on the database or procedure, etc.) when the procedure starts, then reset it when the procedure finishes. This would also account for scenarios where your procedure gets called from within another procedure.

---

If running it from your local Management Studio, the connection will be broken (closed) when SSMS closes.

Any transactions will rollback, all locks will be released. If you're in the middle of some huge data changes, your proc stops running and the connection is still closed, but the rollback will continue anyway

So no need to check based on the facts given...

---

Depends whether you're running it using SqlServerAgent or just via Mgmt Studio.

As @gbn said, once you disconnect then any work using that connection will stop too.

I'd say create a scheduled Job and then it's independent of your connection.

---

If this is part of an application requirement then I would log the start and finish of all of the calls to this stored procedure then have a view against that to show which instances of the stored procedure are currently running.

If this is for maintenance of the database then I would use sp_who or sp_who2.

EDIT: Actually, sp_who and sp_who2 will likely show you the currently running command, not necessarily the stored procedure name itself. You can try using the system dynamic view sys.dm_exec_sql_text, but that isn't always going to be accurate either.