Clustered Index creation uses extremely much diskspace

Hi,

I have a database in which I have a table that is about 20GB (+/- 9.000.000 records).

This table is created in the filegroup 'SRC_TABLES'.

When I want to create my clustered index on this table - in the same filegroup - it starts to write a lot of data to the harddisk. There is only 18GB free on the harddisk so it fails with the error that he can't create enough free space in the filegroup.

This is the query I use. As you can see it's just a standard clustered index query:

ALTER TABLE dbo.SRC_GRIDFEE_LINE_ITEM_SWITCH ADD CONSTRAINT
PK_SRC_GRIDFEE_LINE_ITEM_SWITCH PRIMARY KEY CLUSTERED
(
GRIDFEE_LINE_ITEM_ID
) ON [SRC_TABLES]
GO

I freed up some space and eventually he created the clustered index, but used more than 20GB of HD-space to do so.
I don't understand why he uses so much diskspace to create that clustered index. If the clustered index is finally created it appears to be only about 4GB.

Thanks in advance.

Thread moved from SSIS Forum to something more appropriate.

In answer to the question, try Books Online, for example "Creating Clustered Indexes"

When a clustered index structure is created, disk space for both the old (source) and new (target) structures is required in their respective files and filegroups. The old structure is not deallocated until the complete transaction commits. Additional temporary disk space for sorting may also be required. For more information, see Determining Index Disk Space Requirements.

|||

Thx for moving. Noticed it too late.

For the clustered index... Been there, done that

Doesn't really provide me with the answers that I need.

Anyone?

|||

!! UPDATE !!

According to the properties windows my table is more than 20GB and my indexes are 35MB !?

I have one index on the table --> the primary key which is clustered.

If I look at what can be found in Books Online (look at DarrenSQL's post), this means that SQL Server needs more than 20GB of extra disk space to create a stupid index of only 35MB?

This doesn't make sence to me...

Can anyone help me out here?