How to log a message from t-sql script?

Recently I’ve been working on a long-running script that will be executed directly in a production environment. I needed an easy way to see the progress of T-SQL script execution.

I tried to use PRINT, but it doesn’t output anything until a 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 a 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

WHILE 1=1
BEGIN                                      
    IF @i >= 10
        BREAK;    
    WAITFOR DELAY '00:00:01';                                    
    SET @i = @i + 1        
    RAISERROR ('%d sec running', 10, 1, @i) WITH NOWAIT
END

Results: