January 25, 2004 at 12:26 pm
I have managed to create a backup query for 6 seperate db's but would like to be able to see the progress of each as they are backed up one after the other. I have tried using the STATS command but so far have only managed to get the 10%'s displayed when all the backups are finished, not as each 10% for each db is actually backed up. Any help would be great. Thanks Grindle
January 25, 2004 at 6:38 pm
You may need GO between commands to get the output to display more frequently, also STATS=1 might help get more output. If DBs are very small, might be nothing you can do about this.
We use STATS=1, and because we run backups as SQLAgent jobs we use DBCC OUTPUTBUFFER to monitor the progress whenever we want to. We can see status progress from 0 to 100% for each DB.
January 26, 2004 at 3:34 pm
I have a similar related question. I have a script which uses a cursor to update a large number of records - is there any way of indicating progress (10 recs processed, 20 recs processed etc) just to reassure me that the script is actually running? I cannot use the GO statement as this clears local variables etc and would interfere with program logic. The script is executed via Query Analyser. Thanks for any help.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 28, 2004 at 12:31 am
From another window in QA you should be able to run a query against the table you are updating to see watch the results change by using the NOLOCK hint.
SELECT COUNT(*) FROM FOO WITH (NOLOCK) WHERE UpdatedDate >= @StartDate
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 28, 2004 at 8:33 am
Another way to see ongoing status is RAISERROR with NOWAIT--these messages are output immediately. In many of our long-running utility procs we use an optional @ShowProgress parameter with default value of 0, and an integer counter @n that's incremented every iteration
--update progress every 100 iterations
if ((@ShowProgress = 1) and (@n % 100 = 0))
raiserror('processing row %d ...', 0, 1, @n) with nowait
...
set @n = @n + 1
January 28, 2004 at 4:26 pm
Mike,
Great idea, unfortunately I never think about it until AFTER I start a process and by then it's too late. The other problem is you would have to actually do your update in a loop to be able to call your raiserror.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply