August 4, 2011 at 11:40 pm
Comments posted to this topic are about the item SQL Process % Complete
Mehernosh.
August 5, 2011 at 6:45 am
I tried your script and it doesn't really work for DBCC (not for DBCC CHECKDB anyway): it reports 0% PERCENT_COMPLETE for the entire duration of the execution of DBCC. Then when DBCC completes, of course, it returns nothing.
Rich
August 9, 2011 at 1:28 am
i want count the update feild...howmany time update these feild?
August 9, 2011 at 1:42 pm
Hi Rich, Thank you for the feedback.
You might not see the results of the PERCENT_COMPLETE column if you are using:
DBCC CHECKDB WITH ESTIMATEONLY
I have checked the SQL script and the results of some common options like, Backups, Restores, DBCC CheckDB et al prior to uploading my post. 😀
The value of the command column will show DBCC, then DBCC ALLOC CHECK and then the DBCC TABLE CHECK values. Whilst doing the first two options it does not populate the percent_complete field. I have checked a few variants of the SYS.DM_EXEC_REQUESTS and have checked it on SQL 2005 and 2008 and have checked using different compatibility modes too.
If possible kindly let me know what SQL server version and DBCC command you tried and did not get any values in the Percent_complete column?
Regards
Mehernosh
Mehernosh.
August 9, 2011 at 1:53 pm
Mehernosh (8/9/2011)
Hi Rich, Thank you for the feedback.You might not see the results of the PERCENT_COMPLETE column if you are using:
DBCC CHECKDB WITH ESTIMATEONLYI have checked the SQL script and the results of some common options like, Backups, Restores, DBCC CheckDB et al prior to uploading my post. 😀
The value of the command column will show DBCC, then DBCC ALLOC CHECK and then the DBCC TABLE CHECK values. Whilst doing the first two options it does not populate the percent_complete field. I have checked a few variants of the SYS.DM_EXEC_REQUESTS and have checked it on SQL 2005 and 2008 and have checked using different compatibility modes too.
If possible kindly let me know what SQL server version and DBCC command you tried and did not get any values in the Percent_complete column?
Regards
Mehernosh
Mehernosh, I ran a straightforward DBCC CHECK DB ('mydatabase'), no other flags. Run time is around a minute I'd say.
When I run your query, I get one row returned, with the correct session_id, datatase, start_time, etc. CPU_TIME goes up on subsequent re-executions, as you'd expect. But as the DBCC executes and I continue to re-run your query, PERCENT_COMPLETE remains at 0 and STATUS shows as suspended until the DBCC completes, at which point the query doesn't return any rows.
SQL Server 2005 Enterprise, SP4.
August 10, 2011 at 2:12 pm
Hi Rich, Thanks for the reply.
I have checked the query on my sQL server 2005 enterprise box having SP4 and it appears to be displaying just right. No problems in incrementing the percent_complete counter. 🙂
I have tried different scenarios such as:
Using the master database tried DBCC CHECKDB on its own,
Using the master database tried DBCC CHECKDB ('anotherDatabasename') ,
Using another database tried DBCC CHECKDB on its own,
Using another database tried DBCC CHECKDB ('databasename') et al
And in all cases I have got the percent_complete values populated. I will try to see if there is any other way for it not to display. If it appears to do so than I guess we can highlight these findings to Microsoft tech net or Microsoft connect or the Microsoft SQL Server help desks and log it ?
I do remember reading an article that in SQL server 2005 progress reporting to DBCC CHECKDB was added and we can check the percent_complete column.
Hope this helps?
Regards.
Mehernosh.
August 10, 2011 at 8:27 pm
Mehernosh (8/10/2011)
Hi Rich, Thanks for the reply.I have checked the query on my sQL server 2005 enterprise box having SP4 and it appears to be displaying just right. No problems in incrementing the percent_complete counter. 🙂
I have tried different scenarios such as:
Using the master database tried DBCC CHECKDB on its own,
Using the master database tried DBCC CHECKDB ('anotherDatabasename') ,
Using another database tried DBCC CHECKDB on its own,
Using another database tried DBCC CHECKDB ('databasename') et al
And in all cases I have got the percent_complete values populated. I will try to see if there is any other way for it not to display. If it appears to do so than I guess we can highlight these findings to Microsoft tech net or Microsoft connect or the Microsoft SQL Server help desks and log it ?
I do remember reading an article that in SQL server 2005 progress reporting to DBCC CHECKDB was added and we can check the percent_complete column.
Hope this helps?
Regards.
It's not critical for me, I was just providing you with performance feedback on your script. Not sure why we get different results, but there we are.
Rich
August 10, 2011 at 11:37 pm
Thanks Rich,
Please do check the script with different databases on your SQL box and kindly let me know if the percent_complete column populates the data. I would like to know under what scenario(s) does the SQL script differ? 😀
Regards,
Mehernosh.
August 23, 2011 at 12:14 pm
Hi Rich,
Did you get time to execute the script with different databases on your SQL box? Does the percent_complete column still not display correctly ?
Regards,
Mehernosh.
August 23, 2011 at 12:18 pm
Hi rajvenkat87
Just checked your post. I do not understand your question, Kindly eloborate on it please.
Regards,
Mehernosh.
August 23, 2011 at 12:26 pm
Fair point trstringer. 🙂
You can use the WITH STATS for Backups or restores which can serves as a progress bar and displays the percentage of work done continuously.
Wanted to post a generic SQL to disply the process completion for some commonn SQL commands. (Backup,restores, DBCC and index options et al)
Regards,
Mehernosh.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply