I have a stored procedure that has been running for over 8 hours now in SQL Server 2005. SQL Server Management Studio shows the query is still executing, but I’m not sure if it’s actually making progress or if it’s stuck somewhere.
Is there a way to monitor what’s happening inside the stored procedure while it’s running? I want to see which step it’s currently on or at least confirm that it’s actually doing work and not hanging.
Any suggestions on how to track the execution progress would be really helpful.
To monitor the execution of your stored procedure, you can utilize the dynamic management view sys.dm_exec_requests in combination with DBCC INPUTBUFFER. By querying sys.dm_exec_requests using your session ID, you can inspect the wait_type column—if this is NULL, it indicates that the procedure is actively running. Additionally, if you observe specific wait types, it may suggest blocking issues.
Keep an eye on the reads, writes, and cpu_time columns; an increase in these metrics signifies that work is being performed. If you’re interested in identifying the specific statement currently running within your stored procedure, obtain the sql_handle from dm_exec_requests and execute it against sys.dm_exec_sql_text. This method is particularly effective for monitoring multi-step procedures. Furthermore, reviewing sys.dm_exec_query_stats can provide insights into the historical performance and context of this lengthy execution time.
Interesting case! Have you checked the execution plan while it’s running? You can usually spot which operator’s eating up resources. Also - does your SP have any print statements or temp tables you could check? What made you think it was stuck after 8 hours?
try checking the activity monitor in ssms. it can show you if there’s any blocking or locks. also, you can run select * from sys.dm_os_waiting_tasks to see what’s keeping your spid busy. high cpu usage usually means it’s doing work, not stuck.