Sql-server – Display local variable in while loop

sql serversql-server-2017

I have a while loop that is running for days (bydesign) see related Is there a limit to how many backups can be appended to a single file?

I have a local variable that displays the loop count, when I run it in a query window I can display the count with print @counter

I am now running the while loop in a job, and I want to display the @countervalue. After some research I tried RAISERROR (Transact-SQL) which should display in the SQL error log, but it is not working. I read Using RAISERROR I think I have the code correct, but not sure it can be used in While loop, with a local variable.

Create the database and prep it

USE [master]
GO
Create database T_test

USE [master]
GO
ALTER DATABASE [T_test] SET RECOVERY FULL WITH NO_WAIT
GO

BACKUP DATABASE T_test to disk= N'K:\Test\T_test.bak' with noinit --Create first full
GO

Try running in a query window to test

DECLARE @counter int
SET @counter = 0
WHILE 1=1
BEGIN 
BACKUP DATABASE [T_test] TO  
DISK = N'K:\Test\T_Test.diff' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  
NAME = N'T_Test-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION
SET @counter = @counter + 1;
RAISERROR(N'Count equals :%d', 16, 1, @counter );
print @counter 
END

Stop after a couple of loops and check the SQL logs and the query message

T_Test dot jpeg

The error is not in the SQL logs, but is displayed in the query message.

I don't think you can use GO 5000000 with a local variable, at least in my attempts it resets every time it is re-declared.

How can I display the local variable in a while loop, real time?

Best Answer

There are two extensions of RAISERROR that may help:

To write it to the error log:

DECLARE @msg NVARCHAR(1000) = N''
RAISERROR(@msg, 16, 1) WITH LOG;

To display it in the messages tab in "real time":

DECLARE @msg NVARCHAR(1000) = N''
RAISERROR(@msg, 16, 1) WITH NOWAIT;