How to see progress of running SQL stored procedures?
sql, ssms, t-sql
Solution
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`
Problem
Consider the following stored procedure.. ``` CREATE PROCEDURE SlowCleanUp (@MaxDate DATETIME) AS BEGIN PRINT 'Deleting old data Part 1/3...' DELETE FROM HugeTable1 where SaveDate < @MaxDate PRINT 'Deleting old data Part 2/3...' DELETE FROM HugeTable2 where SaveDate < @MaxDate PRINT 'Deleting old data Part 3/3...' DELETE FROM HugeTable3 where SaveDate < @MaxDate PRINT 'Deleting old data COMPLETED.' END ``` Let's say that each delete statement take a long time to delete, but I like to see the progress of this stored procedure when I'm running it in SQL Management Studio. In other words, I like to see the the output of the PRINT statements to see where I'm at any given time. However, it seems that I can only see the PRINT outputs at the end of the ENTIRE run. Is there a way to make it so that I can see the PRINT outputs at real time? If not, is there any other way I can see the progress of a running stored procedure?