SQL Server 2005 Index Rebuilt

[sendba]

All

My database contains a table which has 900 Million records and there are 4 NC indexes and 1 Clustered Indexes.
At present the occupied drive space for this table is 150 GB and I have 50 GB free space.

When I tried to rebuilt the indexes the 50 GB filled up and it shows no space error.
I am using the following syntax.

ALTER INDEX PK_indexname ON tablename REBUILD with (online = ON)


Is there any better way to optimize the performance and also not making sql server occupy so much space.

Thanks in advance

Sen

 

[mrdenny]

An online rebuild takes a lot of extra space in order to do the rebuild.

If you don't have enough space to complete the operation online you can either do it offline, or try dropping the index and rebuilding it online.

The downside to dropping the index and then creating it is if the create index with online=on fails then you have to do an offline create of the index.

You can also try an online repopulate of the index.

In the end you will probably need to get more storage. As you database grows you will run into these issues more and more.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com