December 7, 2012 at 4:09 am
Scope: Experimental.
When doing maintenance actions (most often for testing), is there a method to see how much progress is made?
For example while clustering/unclustering a table.
Thanks for your time and attention,
Ben Brugman.
:crazy: last night I waited up for a process to finish (clustering) and after a long time the process came to a hold because of diskspace isssues. Database went into recover mode. I did not wait up for that, this morning the database was recovered. Should have gone to bed earlier, but just wanted to know how the clustering was going.:hehe:
December 7, 2012 at 4:33 am
Do you mean Clustered Index ?
http://www.sqlservercentral.com/stairway/72399/
-----------------------------------------------------------------------------
संकेत कोकणे
December 7, 2012 at 5:27 am
sanket kokane (12/7/2012)
Do you mean Clustered Index ?
Yes,
(Yes 'adding' or 'removing' a clustered index).
(Or even doing a simple update off one field over the whole table).
Ben
At this moment I am adding a field to a table ( 70 000 000 plus rows, table is over 30 Gb) After that I am going to cluster on that field plus another field.
December 7, 2012 at 5:52 am
Clustered index defines physical order of your data .
Please go through the link I provided in last post .
SQL server do not give the estimated time required to complete this operation.
check if any blocking is occurring during creating clustered Index.
-----------------------------------------------------------------------------
संकेत कोकणे
December 7, 2012 at 8:04 am
ben.brugman (12/7/2012)
...is there a method to see how much progress is made?For example while clustering/unclustering a table.
there's a dynamic management view for that! sys.dm_exec_requests
http://msdn.microsoft.com/en-us/library/ms177648(v=sql.100).aspx
it has a column percent_complete that lets you know the progress of the following tasks:
Percentage of work completed for the following commands:
ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
CREATE INDEX
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
KILL (Transact-SQL)
RESTORE DATABASE
UPDATE STATISTICS
December 7, 2012 at 9:34 am
it has a column percent_complete that lets you know the progress
Although be careful, as it is not always accurate and very much an estimation.
Accuracy in my experience depends on the type of command you are running.
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply