Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Carat
Yak Posting Veteran
92 Posts |
 Posted - 2009-03-27 : 06:53:40
|
| We have a database that is 85GB large. I'm trying to rebuild the clustered index (primary key) on the biggest table in database (more than 60 million records). While rebuilding the index the database grows to more than 125GB (+40GB). The problem is that I have no free space anymore on the disk, so I get an error.What can I do to rebuild this index?When I use the option 'Sort_In_TempDB' will it also use more than 40GB of space? Our TempDB database is placed on another raid-set but has only 35GB of free space. |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-27 : 08:16:03
|
| If you use table partitioning you can rebuild the index for each partion seperately:http://www.mssqltips.com/tip.asp?tip=1200It should also increase performanace when querying the table etc. |
 |
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 2009-03-27 : 09:32:43
|
| Use Bulked- Recovery Model While Rebuilding index and take transaction log backup in shared path(It will be huge). Change it to full when done. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-27 : 12:41:07
|
| Read this:http://msdn.microsoft.com/en-us/library/ms190203.aspx |
|
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2009-03-30 : 03:16:05
|
| Partioning is an option but at this moment we do not have the time to release this in our production environment. Between this and a few months we are upgrading to SQL Server 2008 by a side-by-side upgrade and we are going to redesign our SAN.We use the Simple recovery model where all the transactions are already minimally logged. We import data into this database only once a month and during this month none of the data is changed. So the use of a Full/Bulk logged recovery model is not necessary. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-30 : 04:36:36
|
| Is the import additional data, or do you drop the table and import a new copy of the table?If it is the second - another option is to create index before import and import data in smaller chunks already sorted by the primary key field. |
|
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2009-04-01 : 06:53:01
|
| We import additional data every month (3GB) so this is not an option in this case.I rebuild the index with success using the 'Sort_In_TempDB' option. Thanks. |
|
|
|
|
|
|
Rebuilding clustered index