Right now table
[dbo].[Impressions] is stored as a HEAP with no special order (last insert goes to the end of the last page) on the
[Impressions] file group.
RID (8 bytes) are used to uniquely identify each record.
CLUSTERED INDEX is created, it changes the way the table is recorded on disk. Rows will be recorded in consecutive pages and ordered by
User_Id. During this operation the whole table has to be copied (under the hood) to its new destination and into its new format: the
CLUSTERED INDEX. Once the index is created, the heap is removed and the space it uses is released.
RID won't be needed anymore and
User_id will be used instead by the
CLUSTERED INDEX. If the size is similar (8 bytes), the new Clustered Index probably requires about the same size as the original table.
This suppose and I guess that
User_id is unique. If this is not the case, row with non unique
User_id will also use a 4 bytes value (internally generated) called a
uniqueifier. This could lead to some extra space being used. If the current heap table contains
NONCLUSTERED INDEX on the same file group (
[Impressions]), their updates may also require some extra space.
The script contains
SORT_IN_TEMPDB = OFF and it mentions
because the 'Impressions' filegroup is full. Therefore
tempdb seems fine. File group
Impressions is the real problem. It has only 1TB free when the new table structure will require at least roughly 1.4TB.
The best solution here is to add enough space to file group
[Impressions] (i.e. ~1.5 TB total) and enough space to the disk if the disk itself is full.
This is explained under Disk Space Requirements in SORT_IN_TEMPDB Option For Indexes:
When you create a clustered index on a table that does not have nonclustered indexes, you must have available as free space:
If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs.These include the data rows of the table.There must be sufficient free space in the destination filegroup to store the final index structure.This includes the data rows of the table and the index B-tree.You may have to adjust the estimate for factors such as having a large key size or a fill factor with a low value.
If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table.This includes the index structure.The continuity of the table and index extents may be improved if more free space is available.
When you create a clustered index on a table that has nonclustered indexes, you must have available as free space:
Space is also needed to store intermediate sort result in either the DB (
SORT_IN_TEMPDB = OFF) or in
SORT_IN_TEMPDB = ON) as mentioned in CREATE INDEX (Transact-SQL):
Creating a clustered index on a table (heap) or dropping and re-creating an existing clustered index requires additional workspace to be available in the database to accommodate data sorting and a temporary copy of the original table or existing clustered index data.
Aside from this error, setting
ON could be a good choice on such a big table. This will store index creation transaction log in the tempdb logs and won't mix it with transactions recorded by user actions. For more information, read Transaction Log Disk Space for Index Operations.
Data copy to a new table on another file group could be an option. However this option may require more IO (i.e. more than the CI creation) on both file groups (Read from Impression and Write to NewFG) and a huge amount of transaction on the
HULU log file. This could lead to a new space and
file group is full issue on another file.
Partition switch can't be used because the table structure are not compatible. Row in HEAP are not ordered and are not recorded in the same fashion. It also must be performed on the same file group. For more information, read Transferring Data Efficiently by Using Partition Switching.