May 1, 2011 at 9:07 am
Hi,
I ran
'ALTER INDEX ALL ON tablename REBUILD WITH (FILLFACTOR=90, MAXDOP=0)'
on compatibility 80 database
On a huge table with a massive index , Datafile grew ran out of disk space and rebuild failed, there is hardly any feespace now I thought it would have reverted to the same free space before it failed?
Whats happened?
Many thanks
May 1, 2011 at 9:12 am
A rebuild always needs free space, often the size of the index.
A data file will never shrink on its own. Only a shrink will do that, and it will fragment every single index in your database if you do (so you'll need to rebuild, which will grow the file, etc)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2011 at 9:34 am
many thanks
May 2, 2011 at 10:33 am
One possible solution to your problem (not how to regain your space, but how to rebuild your indexes if you don't have enough space) would be to create an additional file/filegroup on another drive. You can either move your large table over to that filegroup (which could net you some performance gains as well) or just add an additional file for primary, so that you can grow past the size of the one drive.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply