I have a stored procedure running on sql server 2008 looping over a table of ~50 million rows, deleting records one day at a time (approx. 25,000 records a day). I want to be able to watch this occur as the process runs via print statements to the messages window. I'd like to be able to see a message each time a day's worth of deletions is committed. Is there any way to do something like this? The procedure is roughly laid out like this:
WHILE EXISTS(<query statement>)
BEGIN
BEGIN TRAN
DELETE
FROM <table>
WHERE <condition>
AND <condition>
--need some sort of "rows affected" statement here
COMMIT
END
Unfortunately, PRINT statement output is spooled up and not written to the output immediately. A way round this is to use RAISERROR like this:
RAISERROR ('MY MESSAGE', 0, 1) WITH NOWAIT
Specifically for what you want:
DECLARE @Msg VARCHAR(200)
WHILE EXISTS(<query statement>)
BEGIN
BEGIN TRAN
DELETE
FROM <table>
WHERE <condition>
AND <condition>
SELECT @Msg = CAST(@@ROWCOUNT AS VARCHAR(10)) + ' rows affected'
RAISERROR (@Msg, 0, 1) WITH NOWAIT
COMMIT
END
I also tend to include the current time in the message so I can record progress against time.
Anything more complex than select @@rowcount ?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With