November 7, 2011 at 10:11 am
GilaMonster (11/7/2011)
Ninja's_RGR'us (11/7/2011)
I don't know those exact internals but it would make sense to copy the index from tempdb to the local db before dropping the old one.The index is not copied anywhere. The options is sort_in_temp_db. If it's on, then TempDB is used for the extra space to sort as opposed to the user database (that 20% that I mentioned a few times). It's not the index build that's moved to tempDB, that would be really silly for huge indexes.
ya that makes more sense.
Tx Gail.
November 7, 2011 at 10:35 am
sqlfriends (11/7/2011)
Ninja's_RGR'us (11/7/2011)
So when you rebuild you need AT LEAST another 4.8 GB of free space (unless doing it in tempdb). Since you only have 3.7GB left you have this error.Thank you, but I see in maintenance plan we do check sort in tempdb.
And also the table does have a clustered index.
The clustered index is being rebuilt as well so you'll need over 4GB to rebuild the indexes even with the sort in tempdb option.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 7, 2011 at 11:01 am
GilaMonster (11/7/2011)
SQLRNNR (11/7/2011)
GilaMonster (11/7/2011)
SQLRNNR (11/7/2011)
We really need to know if that table has a CI. Without it, you can't defrag the other indexes.Yes, you can.
It has always failed for me. How do we (without creating CI and then dropping it after the defrag)?
Same way as for tables that have a clustered index - ALTER INDEX <nonclustered index name> REBUILD.
The heap itself can't be, but the nonclustered indexes built on top of the heap absolutely can be rebuilt.
K. I must have been running into hokiness because I would still see same frag% after that statement as before that statement.
It is good to know that it works though.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 7, 2011 at 11:34 am
Jack Corbett (11/7/2011)
sqlfriends (11/7/2011)
Ninja's_RGR'us (11/7/2011)
So when you rebuild you need AT LEAST another 4.8 GB of free space (unless doing it in tempdb). Since you only have 3.7GB left you have this error.Thank you, but I see in maintenance plan we do check sort in tempdb.
And also the table does have a clustered index.
The clustered index is being rebuilt as well so you'll need over 4GB to rebuild the indexes even with the sort in tempdb option.
Thanks all, the table does have a clustered index.
And I do a query sys.dm_db_index_physical_stats
The result is in the attachment
Viewing 4 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply