February 16, 2010 at 3:49 am
I'm having troubling rebuilding a Clustered Index on a DataWarehouse table due to Disk Space constraints - as TempDB blows-up having used all the available Disk Space. My TEMPDB grew to 100GB which is the current limitation of the Disks I have available.
"Could not allocate space for object 'dbo.SORT temporary run storage: xxxx' in database 'tempdb' because the 'PRIMARY' filegroup is full"
The Index was badly fragmented and initially I tried a rebuild (same error ... but on the DB file rather than TempDB). It is Non-Unique Clustered Index and is keyed simple on "EventTime" [Datetime] column. The table has nearly 570million rows - occupies > 250GB as below.
name rows reserved data index_size unused
------------------- ----------- ------------------ ------------------ ------------------ ------------------
EventDetails 569236411 259831360 KB 259797280 KB 512 KB 33568 KB
I'm aware that an Index build requires Workspace which will expand the current DB or TempDB - but either way I'm running into Storage constraints - despite my attempts to squeeze as much free disk space out of the box. I've now Dropped the Index ... so there are no indexes on the table.
I'm not 100% certain how to calculate the estimated the Space Required to build an Index - can someone help.
Have other people overcome this requirement by recreating the table or moving the data in stages ... although I can't think of any way that will enable this and require less working space.
February 16, 2010 at 7:26 am
In a clustered index the index leaf level is the actual data e.g. rows in the table are physically kept sorted by the columns referenced in the clustered index.
SQL Server is trying to sort the 570 million rows by clustered index key and move the table - including the clustered index - to a different physical location, that's why you are seeing such a large usage of temp space.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 17, 2010 at 6:36 am
Thanks Paul ... your post cements my thinking.
In short .. the various sites/blogs which estimate sizing of Indexes should be simplied to:
If you ever need to rebuild a Clustered Index on a Large Table .. you'll need the equivalent space of that your data occupies the table - in order to Rebuild/Recreate the Index.
I'll continue my painful shuffling of data on the disk to free up the necessary huge space I require.
Cheers, Unsy
February 17, 2010 at 9:53 pm
I had exactly the problem. Not as much space available as the largest table I need to rebuild the clustered index on. I solved the problem by implementing horizontal partitioning on the table. Now, each partition only contains 2gb of data each - and you can rebuild the clustered index on each partition at a time.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply