How to See Progress of Running SQL Stored Procedures

How to see progress of running SQL stored procedures?

If you use RAISERROR with a severity of 10 or less, and use the NOWAIT option, it will send an informational message to the client immediately:

RAISERROR ('Deleting old data Part 1/3' , 0, 1) WITH NOWAIT

Check if stored procedure is running

You might query sys.dm_exec_requests which will provide sesion_ID, waittime and futher rows of interest and CROSS APPLY sys.dm_exec_sql_text filtering your query with the SQL for your procedure.

Select * from
(
SELECT * FROM sys.dm_exec_requests 
where sql_handle is not null
) a 
CROSS APPLY  sys.dm_exec_sql_text(a.sql_handle) t 
where t.text like 'CREATE PROCEDURE dbo.sp_sleeping_beauty%'

Check if a stored proc is running?

I asked this once :)

Check out:

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

---

Related Topics