Running stored procedures

Question

Hi,

What's the best way to know which stored procedure are currently running in a database.

I have a stored procedure which basically calls other 3 and passes them some parameters and these 3 stored procedures take a long time to complete... I would like to know which one is running...

Thanks

Answer 1

Use Activity Monitor to detect what is currently running on your database. The Command column might indicate which stored procedure is currently running.

To help you monitor whats stored procedure is running I would suggest creating a SQL Job to run those 3 stored procedures seperately as steps. That way you can place an email alert step in between them so that you know when each one has completed.

This shouldn't be too difficult to setup in SQL Server Agent.

EDIT: SQL Profiler is an option, but this will have an impact on performance as you will be monitoring the live database, plus you indicated that the stored procedures take a while to run so you would want those times to increase. IMO I think a simple email alert, or some other form of notification which could be built at the end of each stored procedure would be you best option.

e.g. An simple insert to a log file with a timestamp indication when each stored procedure has finished.

Answer 2

I think you can also use SQL profiler to get information in more detail.