Help! Indexing LARGE table

OK, I imported 680 million records into an unindexed table. That went well.

Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.

It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?

SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
Windows 2000 SP4
4GB RAM (although I just noticed the 3GB OS switch wasn't on)
SCSI boot drive
tempdb, data, and transaction log are on a FibreChannel RAID SAN

Help! Thanks in advance!OK, I imported 680 million records into an unindexed table. That went well.

Well what? Into the night?

Then, I went into Enterprise Manager and added a two column non-unique clustered index to that table to speed access.

Yeah, you'd want to do that with almost a billion records (hey let's not split hairs)

It's been running for ~36 hours and I have no idea when it will complete. I have deadlines that I'm going to miss and am very nervous; what can I do?

Pray?

SQL Server 2000 Enterprise Edition (8.00.818 - sp3 + hotfixes)
Dual 3Ghz Xeon (two physical CPUs each have HyperThreading enabled)
Windows 2000 SP4
4GB RAM (although I just noticed the 3GB OS switch wasn't on)
SCSI boot drive
tempdb, data, and transaction log are on a FibreChannel RAID SAN

Nice box...do you have to buy it dinner?

Help! Thanks in advance!

Help with what? You defenitley DONT want to kill it...it'll take twice as long to rollback.

Seems you get a lot of these. I would definetly start padding my estimates...

OR, I would think about performance before...

Without knowing you're data, I would still say that this is a MAJOR partition canidate, into many file groups on many different devices...

But that's just me

OH

MOO

Good Luck|||I agree with Brett that killing the job will be bad. A clustered index looks to order the rows of the table on the disk itself. If SQL Server decides that these rows are in exactly the revers order of what they should be, then you are going to see an awful lot of data move around. Your transaction log is probably going nuts, too.
As for speeding it up, there isn't a lot you can do while the process is in flight. If this import is a normal thing, then you can consider partitioning the table on some unique key, and cutting it down to seven 100 million row tables. Also, for non-clustered indexes, you can get a boost from the SORT_IN_TEMPDB option. Take a look in BOL for that.|||Without knowing you're data, I would still say that this is a MAJOR partition canidate, into many file groups on many different devices...

Yes, horizontal partitioning will be a necessary move. My data isn't primarily organized by date so that wouldn't make an ideal partition criteria. I haven't done this before and am nervous about doing it correctly; mistakes at the planning phase will be hard to correct.

Your reply is a mix of helpful advice and frustrated sarcasm. I am thankful for the helpful advice and probably deserve the sarcasm.|||ok, it just finished rolling back. The data file was on a 150GB SAN partition and ran out of space. It was previously like 73GB so I'm surprised that happened. any way, at least I have the database back...

thanks guys|||Your reply is a mix of helpful advice and frustrated sarcasm. I am thankful for the helpful advice and probably deserve the sarcasm.

Well I was trying to get you to laugh..not to make fun of you

Why not post the DDL of the table...|||Got me to laugh, anyway. Does that count as a near miss?

Roger: At this point, maybe you can try the index as a non-clustered index. Let us know what happens.|||Is there any chance the import file is already sorted? If so you can create the clustered index on the empty table and use the ORDERED argument with BCP to prevent SQL from resorting the data.|||Well I was trying to get you to laugh..not to make fun of you

Brett's gone off to Flordia for vacation. We can make fun of him while he is gone. :p

Help! - Error in moving data from AS400

I have a process that simply copies data from a table on an AS400 to SQL Server. This process executes fine until it gets to around 44 million rows copied out of 48 million rows in the source table. At this point, the process exits with the following error messages:

The attempt to send a row to SQL Server failed with error code 0x80004005.

The ProcessInput method on component "SQL Server Destination" (40) failed with error code 0xC02020C7.
The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Thread "WorkThread0" has exited with error code 0xC02020C7.

The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Thread "SourceThread0" has exited with error code 0xC0047038.

I am using the Microsoft OLEDB Provider for DB2 to access the AS400 and have tried the Table/View and SQL Command access modes with the same result.

Any help will be Greatly Appreciated!

-Mark Redman.

Hi Mark

The error was from your SQL Server Destination rather than from the DB2 source.

Which connection you have at your destination and which provider?

Thanks
Wenyang|||Hi Mark

This is not directly related to your problem, but I am also trying to use the MS OLE DB Provider for DB2 for connecting to an AS400, but I cannot make it work... How are your parameters setup? Could you perhaps post a screenshot?|||I am using the SQL Native Provider as the destination. It is a SQL Server 2005 Release Version server.|||The Parameters I have filled in are:

APPC Mode Name: QPCSUPP
APPC Security Type: Program
Defer Prepare: False
Derive Parameters False
Host CCSID 37
Network Address BKL400 <this would be different for you>
Network Port 446
Network Transport Library TCPIP
Package Collection QSYS2
PC Code Page 1252
Data Source bkl400
Initial Catalog bkl400
I hope this helps!|||The error was from your destination provider. Seems to me it's not related to the source you used in your package. I'm not sure whether you are using MS OLEDB for ODBC, if that's the case, search online using the error code 0x80004005 may bring you to some helpful web sites.

Thanks
Wenyang|||

The destination provider I am using is the Native OLEDB/SQL Native Client (SQLNCLI).