How can I know which stored procedure is running in SQL Server 2005

Question

How can I know which stored procedure is running in SQL Server 2005. And also how to print that SP in the query?

Answer 1

You can use SQL Profiler and Activity Manager

you can use

sp_helptext [sp_name]

to watch SP contents

Answer 2

SQL Profiler is usually a good start to log activity on a SQL Server instance

Answer 3

You can use the Activity Monitor in SQL Server Management Studio. It's located in the Management node. Once you have the Activity Monitor open have a look at the Command column to see what command is being executed. SQL Profiler is another option but it takes a bit more work to setup.

There's also an article here on how to monitor SPs.

Answer 4

In SQL Server 2005 and 2008, if you are trying to troubleshoot performance or behavior in a production system, you should be using the dynamic management views. Sys.dm_exec_requests will show you active transactions including useful information such as when it started, what the last lock was, if it's blocked, the blocking process, etc. You can also use sys.dm_exec_query_stats to see aggregated statistics of queries that are have been running and are still in cache. This will show interesting data like average run time, min/max run time, min/max/avg cpu, etc.

Both these can then be used to link to other dmv's to retrieve execution plans, queries, and all kinds of other interesting information, on the fly, for cheap. It's much better than trying to maintain a set of trace events in most cases.