Sql Server print affected rows during while loop

Question

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

Answer 1

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.

Answer 2

Anything more complex than select @@rowcount ?

Answer 3

How about using the OUTPUT clause?? Can you write to the SQL Server console from your stored proc??

Like in a trigger, you have a "Deleted" pseudo-table at your hands, which contains the rows that are being deleted, with all their columns.

WHILE EXISTS(<query statement>)
BEGIN
  BEGIN TRAN

  DELETE FROM  <table>
  OUTPUT deleted.ID, deleted.TimeStamp -- or whatever
  WHERE <condition>
  AND <condition>

  COMMIT

END

Answer 4

I used to do something similar to this, and because prints where spooled up it was not much use, however I discovered that the command line utility osql outputs them immediately, so I wrote the stored proc and called it from a batch file instead, which let me see the progress.