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 @counter
value. 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
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:
To display it in the messages tab in "real time":