Recently I've been working on a long running script that will be executed directly in PROD enviroment. I needed an easy way to see a progress of t-sql script execution.
I tried to use PRINT, but it doesn’t output anything until script execution is done.
You can use some log table and insert progress status messages there, but there is an easier way:
RAISERROR ('Message', 10, 1) WITH NOWAIT
RAISERROR with severity of 10 or less doesn't trigger try catch block and can be used for log purposes. Here is a little longer example:
DECLARE @i INT = 0
IF @i >= 10
WAITFOR DELAY '00:00:01';
SET @i = @i + 1
RAISERROR ('%d sec running', 10, 1, @i) WITH NOWAIT