BCP out/in very large table

Could someone give me some guidance on the fastest way to unload and reload
data from very large tables. I am planning to drop about 30% of columns
(fixed length columns) from few of our largest tables (size ranges from 300
GB to 500 GB each). In order to reclaim space from dropped columns, looks
like only way is to unload the data, recreate the table (with less columns)
and reload the data. Dropping and recreating clustered index didn't reclaim
space ( I am running SQL 2000 sp4).
I am planning to perform following steps:
1. BCP out data
2. Drop/recreate table with only clustered index
3. BCP in data
4. Create non clustered index, foreign key, constraints etc
Do you see any potential problem with exporting such a big size to text
file? I am planning to export the data to multiple files so that when I BCP
in, I can load them concurrently and also planning to use ORDER and TABLOCK
hint. Can I use ORDER and TABLOCK hint when loading data to a table with
clustered index and loading multiple file simultaneously? I am guessing if I
use "queryout" option with "order by" clause on clustered column when bcp
out the data, I should be able to use ORDER hint when loading data in. Let
me know if I am wrong. Also, I am planning to use SQL server Native format.
If you have any experience in unloading/reloading very large table from sql
server, I would love to hear your comments/suggestion.
Instead of BCP in, consider BULK INSERT with parallel streams. See the
following excellent articles for some of the best rpactices recommendations:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx
http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx
http://www.microsoft.com/sql/techinfo/administration/2000/rosetta.doc
Linchi
"james" wrote:

> Could someone give me some guidance on the fastest way to unload and reload
> data from very large tables. I am planning to drop about 30% of columns
> (fixed length columns) from few of our largest tables (size ranges from 300
> GB to 500 GB each). In order to reclaim space from dropped columns, looks
> like only way is to unload the data, recreate the table (with less columns)
> and reload the data. Dropping and recreating clustered index didn't reclaim
> space ( I am running SQL 2000 sp4).
> I am planning to perform following steps:
> 1. BCP out data
> 2. Drop/recreate table with only clustered index
> 3. BCP in data
> 4. Create non clustered index, foreign key, constraints etc
> Do you see any potential problem with exporting such a big size to text
> file? I am planning to export the data to multiple files so that when I BCP
> in, I can load them concurrently and also planning to use ORDER and TABLOCK
> hint. Can I use ORDER and TABLOCK hint when loading data to a table with
> clustered index and loading multiple file simultaneously? I am guessing if I
> use "queryout" option with "order by" clause on clustered column when bcp
> out the data, I should be able to use ORDER hint when loading data in. Let
> me know if I am wrong. Also, I am planning to use SQL server Native format.
> If you have any experience in unloading/reloading very large table from sql
> server, I would love to hear your comments/suggestion.
>
>
sql

BCP out/in very large table

Could someone give me some guidance on the fastest way to unload and reload
data from very large tables. I am planning to drop about 30% of columns
(fixed length columns) from few of our largest tables (size ranges from 300
GB to 500 GB each). In order to reclaim space from dropped columns, looks
like only way is to unload the data, recreate the table (with less columns)
and reload the data. Dropping and recreating clustered index didn't reclaim
space ( I am running SQL 2000 sp4).
I am planning to perform following steps:
1. BCP out data
2. Drop/recreate table with only clustered index
3. BCP in data
4. Create non clustered index, foreign key, constraints etc
Do you see any potential problem with exporting such a big size to text
file? I am planning to export the data to multiple files so that when I BCP
in, I can load them concurrently and also planning to use ORDER and TABLOCK
hint. Can I use ORDER and TABLOCK hint when loading data to a table with
clustered index and loading multiple file simultaneously? I am guessing if I
use "queryout" option with "order by" clause on clustered column when bcp
out the data, I should be able to use ORDER hint when loading data in. Let
me know if I am wrong. Also, I am planning to use SQL server Native format.
If you have any experience in unloading/reloading very large table from sql
server, I would love to hear your comments/suggestion.Instead of BCP in, consider BULK INSERT with parallel streams. See the
following excellent articles for some of the best rpactices recommendations:
http://www.microsoft.com/technet/pr.../19/602142.aspx
http://www.microsoft.com/sql/techin...000/rosetta.doc
Linchi
"james" wrote:

> Could someone give me some guidance on the fastest way to unload and reloa
d
> data from very large tables. I am planning to drop about 30% of columns
> (fixed length columns) from few of our largest tables (size ranges from 30
0
> GB to 500 GB each). In order to reclaim space from dropped columns, looks
> like only way is to unload the data, recreate the table (with less columns
)
> and reload the data. Dropping and recreating clustered index didn't reclai
m
> space ( I am running SQL 2000 sp4).
> I am planning to perform following steps:
> 1. BCP out data
> 2. Drop/recreate table with only clustered index
> 3. BCP in data
> 4. Create non clustered index, foreign key, constraints etc
> Do you see any potential problem with exporting such a big size to text
> file? I am planning to export the data to multiple files so that when I BC
P
> in, I can load them concurrently and also planning to use ORDER and TABLO
CK
> hint. Can I use ORDER and TABLOCK hint when loading data to a table with
> clustered index and loading multiple file simultaneously? I am guessing if
I
> use "queryout" option with "order by" clause on clustered column when bcp
> out the data, I should be able to use ORDER hint when loading data in. Let
> me know if I am wrong. Also, I am planning to use SQL server Native format
.
> If you have any experience in unloading/reloading very large table from sq
l
> server, I would love to hear your comments/suggestion.
>
>

BCP import large text file to sql tables

hi,
I have a huge text file(about 10 GB) that needed to imported to SQL server2k
table.
I know BCP is the fastest way to do it. But if I use BPC, my log shipping to
a prod standby serve will not have BPC transactions.
What should I do to have fast importing process and log shipping synched as
well.
Thanks
How big is the entire db? If it is small compared to 10GB I would just
disable log shipping, import, index as appropriate then backup and restore
full db and restart log shipping. If the db size is very large compared to
10GB you will be better off letting log shipping do it's thing. If you bcp
with FULL recovery mode and use small batch sizes won't log shipping work
fine?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
> hi,
> I have a huge text file(about 10 GB) that needed to imported to SQL
> server2k
> table.
> I know BCP is the fastest way to do it. But if I use BPC, my log shipping
> to
> a prod standby serve will not have BPC transactions.
> What should I do to have fast importing process and log shipping synched
> as
> well.
> Thanks
>
|||the size of Is it related to importing process? The db is 250gb
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k61tbvko3h51@.corp.supernews.com...
> How big is the entire db? If it is small compared to 10GB I would just
> disable log shipping, import, index as appropriate then backup and restore
> full db and restart log shipping. If the db size is very large compared
> to 10GB you will be better off letting log shipping do it's thing. If you
> bcp with FULL recovery mode and use small batch sizes won't log shipping
> work fine?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>
|||That is pretty big in relation to the 10GB to be loaded. I would consider
keeping log shipping online to avoid the 'downtime' required to completely
resync the database after the load. YMMV.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
> the size of Is it related to importing process? The db is 250gb
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13k61tbvko3h51@.corp.supernews.com...
>
|||I have weekly large import -- 20GB text file(35 million records) need to
insert into 2 tables _ i need performance as well.
What is the best way to acomplish this with fullly log shipping synched.
Thanks
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k66ifi8esog81@.corp.supernews.com...
> That is pretty big in relation to the 10GB to be loaded. I would consider
> keeping log shipping online to avoid the 'downtime' required to completely
> resync the database after the load. YMMV.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
>

BCP import large text file to sql tables

hi,
I have a huge text file(about 10 GB) that needed to imported to SQL server2k
table.
I know BCP is the fastest way to do it. But if I use BPC, my log shipping to
a prod standby serve will not have BPC transactions.
What should I do to have fast importing process and log shipping synched as
well.
ThanksHow big is the entire db? If it is small compared to 10GB I would just
disable log shipping, import, index as appropriate then backup and restore
full db and restart log shipping. If the db size is very large compared to
10GB you will be better off letting log shipping do it's thing. If you bcp
with FULL recovery mode and use small batch sizes won't log shipping work
fine?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
> hi,
> I have a huge text file(about 10 GB) that needed to imported to SQL
> server2k
> table.
> I know BCP is the fastest way to do it. But if I use BPC, my log shipping
> to
> a prod standby serve will not have BPC transactions.
> What should I do to have fast importing process and log shipping synched
> as
> well.
> Thanks
>|||the size of Is it related to importing process? The db is 250gb
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k61tbvko3h51@.corp.supernews.com...
> How big is the entire db? If it is small compared to 10GB I would just
> disable log shipping, import, index as appropriate then backup and restore
> full db and restart log shipping. If the db size is very large compared
> to 10GB you will be better off letting log shipping do it's thing. If you
> bcp with FULL recovery mode and use small batch sizes won't log shipping
> work fine?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>|||That is pretty big in relation to the 10GB to be loaded. I would consider
keeping log shipping online to avoid the 'downtime' required to completely
resync the database after the load. YMMV.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
> the size of Is it related to importing process? The db is 250gb
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13k61tbvko3h51@.corp.supernews.com...
>|||I have weekly large import -- 20GB text file(35 million records) need to
insert into 2 tables _ i need performance as well.
What is the best way to acomplish this with fullly log shipping synched.
Thanks
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k66ifi8esog81@.corp.supernews.com...
> That is pretty big in relation to the 10GB to be loaded. I would consider
> keeping log shipping online to avoid the 'downtime' required to completely
> resync the database after the load. YMMV.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
>

BCP import large text file to sql tables

hi,
I have a huge text file(about 10 GB) that needed to imported to SQL server2k
table.
I know BCP is the fastest way to do it. But if I use BPC, my log shipping to
a prod standby serve will not have BPC transactions.
What should I do to have fast importing process and log shipping synched as
well.
ThanksHow big is the entire db? If it is small compared to 10GB I would just
disable log shipping, import, index as appropriate then backup and restore
full db and restart log shipping. If the db size is very large compared to
10GB you will be better off letting log shipping do it's thing. If you bcp
with FULL recovery mode and use small batch sizes won't log shipping work
fine?
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
> hi,
> I have a huge text file(about 10 GB) that needed to imported to SQL
> server2k
> table.
> I know BCP is the fastest way to do it. But if I use BPC, my log shipping
> to
> a prod standby serve will not have BPC transactions.
> What should I do to have fast importing process and log shipping synched
> as
> well.
> Thanks
>|||the size of Is it related to importing process? The db is 250gb
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k61tbvko3h51@.corp.supernews.com...
> How big is the entire db? If it is small compared to 10GB I would just
> disable log shipping, import, index as appropriate then backup and restore
> full db and restart log shipping. If the db size is very large compared
> to 10GB you will be better off letting log shipping do it's thing. If you
> bcp with FULL recovery mode and use small batch sizes won't log shipping
> work fine?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> hi,
>> I have a huge text file(about 10 GB) that needed to imported to SQL
>> server2k
>> table.
>> I know BCP is the fastest way to do it. But if I use BPC, my log shipping
>> to
>> a prod standby serve will not have BPC transactions.
>> What should I do to have fast importing process and log shipping synched
>> as
>> well.
>> Thanks
>>
>|||That is pretty big in relation to the 10GB to be loaded. I would consider
keeping log shipping online to avoid the 'downtime' required to completely
resync the database after the load. YMMV.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
> the size of Is it related to importing process? The db is 250gb
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13k61tbvko3h51@.corp.supernews.com...
>> How big is the entire db? If it is small compared to 10GB I would just
>> disable log shipping, import, index as appropriate then backup and
>> restore full db and restart log shipping. If the db size is very large
>> compared to 10GB you will be better off letting log shipping do it's
>> thing. If you bcp with FULL recovery mode and use small batch sizes
>> won't log shipping work fine?
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> hi,
>> I have a huge text file(about 10 GB) that needed to imported to SQL
>> server2k
>> table.
>> I know BCP is the fastest way to do it. But if I use BPC, my log
>> shipping to
>> a prod standby serve will not have BPC transactions.
>> What should I do to have fast importing process and log shipping synched
>> as
>> well.
>> Thanks
>>
>>
>|||I have weekly large import -- 20GB text file(35 million records) need to
insert into 2 tables _ i need performance as well.
What is the best way to acomplish this with fullly log shipping synched.
Thanks
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13k66ifi8esog81@.corp.supernews.com...
> That is pretty big in relation to the 10GB to be loaded. I would consider
> keeping log shipping online to avoid the 'downtime' required to completely
> resync the database after the load. YMMV.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> the size of Is it related to importing process? The db is 250gb
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13k61tbvko3h51@.corp.supernews.com...
>> How big is the entire db? If it is small compared to 10GB I would just
>> disable log shipping, import, index as appropriate then backup and
>> restore full db and restart log shipping. If the db size is very large
>> compared to 10GB you will be better off letting log shipping do it's
>> thing. If you bcp with FULL recovery mode and use small batch sizes
>> won't log shipping work fine?
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> hi,
>> I have a huge text file(about 10 GB) that needed to imported to SQL
>> server2k
>> table.
>> I know BCP is the fastest way to do it. But if I use BPC, my log
>> shipping to
>> a prod standby serve will not have BPC transactions.
>> What should I do to have fast importing process and log shipping
>> synched as
>> well.
>> Thanks
>>
>>
>>
>|||Depending on the network pipe your log shipping goes across you may need to
split the file into numerous smaller files to avoid flooding the log
shipping system.
I would also consider dropping and recreating indexes if the 35M rows is a
large fraction of the total rows in the tables. This should greatly speed
the data load and will give you cleaner indexes to boot.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u$Np9G6KIHA.3848@.TK2MSFTNGP05.phx.gbl...
>I have weekly large import -- 20GB text file(35 million records) need to
>insert into 2 tables _ i need performance as well.
> What is the best way to acomplish this with fullly log shipping synched.
> Thanks
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13k66ifi8esog81@.corp.supernews.com...
>> That is pretty big in relation to the 10GB to be loaded. I would
>> consider keeping log shipping online to avoid the 'downtime' required to
>> completely resync the database after the load. YMMV.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:e95zUi5KIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> the size of Is it related to importing process? The db is 250gb
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13k61tbvko3h51@.corp.supernews.com...
>> How big is the entire db? If it is small compared to 10GB I would just
>> disable log shipping, import, index as appropriate then backup and
>> restore full db and restart log shipping. If the db size is very large
>> compared to 10GB you will be better off letting log shipping do it's
>> thing. If you bcp with FULL recovery mode and use small batch sizes
>> won't log shipping work fine?
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:e67Yi74KIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> hi,
>> I have a huge text file(about 10 GB) that needed to imported to SQL
>> server2k
>> table.
>> I know BCP is the fastest way to do it. But if I use BPC, my log
>> shipping to
>> a prod standby serve will not have BPC transactions.
>> What should I do to have fast importing process and log shipping
>> synched as
>> well.
>> Thanks
>>
>>
>>
>>
>