 | |
Re: DTS BULK INSERT AND ABNORMAL FILEGROUP SPACE
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: DTS BULK INSERT AND ABNORMAL FILEGROUP SPACE -
07-09-2003
, 02:13 AM
|
We use a dts package to copy a database of about 7 gigas to another server. ( bulk insert) The target database with same structure and same datas needs 14 gigas of space. We tried a shrink database on the target with no success. One table of 1 giga on the source needs 8 gigas on target. I think that the package create the table , populate it with bulk insert an then create indexes and constraint. Why such a difference of size ? Thanks a lot for help |
#2
| |||
| |||
|
|
-----Original Message----- What is the proportion of data to log ? Do you have the insert batch size set to 0 (All records in 1 go) ? what is the recovery model of the DB ? Are you pouring in data with indices still on the table ? Here is what I did this weekend. I had to load 3 * 4 GB tables (9million rows in each) I scripted out the indices on the tables and dropped them I changed the recovery model to simple I had a batch size of 500 After the load of the data I reapplied the indices interspersed wth GO statements to allow SQL Server to get rid of the transactions from the log This resulted in a DB that had no bloated log. I backed the DB up and changed the recovery model back. Your mileage may vary. -- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Ariane Mathieu" <ariane.mathieu (AT) Tixis (DOT) Arcelor.com> wrote in message news:012e01c34562$e9024ee0$a101280a (AT) phx (DOT) gbl... We use a dts package to copy a database of about 7 gigas to another server. ( bulk insert) The target database with same structure and same datas needs 14 gigas of space. We tried a shrink database on the target with no success. One table of 1 giga on the source needs 8 gigas on target. I think that the package create the table , populate it with bulk insert an then create indexes and constraint. Why such a difference of size ? Thanks a lot for help . |
#3
| |||
| |||
|
|
Log is on a different filegoup and grows normally Recovery model is simple. The dts package generates all scripts and bulk insert (not bcp), i haven't found batch size. I think it's more a problem of allocation of data pages because we have the same kind of problem with insert in a transaction. Is there a way to do a reorg on a table on a sql2000 server ? ( not manually with unload/reload ) I hope my english is not too bad. Thanks for help. -----Original Message----- What is the proportion of data to log ? Do you have the insert batch size set to 0 (All records in 1 go) ? what is the recovery model of the DB ? Are you pouring in data with indices still on the table ? Here is what I did this weekend. I had to load 3 * 4 GB tables (9million rows in each) I scripted out the indices on the tables and dropped them I changed the recovery model to simple I had a batch size of 500 After the load of the data I reapplied the indices interspersed wth GO statements to allow SQL Server to get rid of the transactions from the log This resulted in a DB that had no bloated log. I backed the DB up and changed the recovery model back. Your mileage may vary. -- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Ariane Mathieu" <ariane.mathieu (AT) Tixis (DOT) Arcelor.com> wrote in message news:012e01c34562$e9024ee0$a101280a (AT) phx (DOT) gbl... We use a dts package to copy a database of about 7 gigas to another server. ( bulk insert) The target database with same structure and same datas needs 14 gigas of space. We tried a shrink database on the target with no success. One table of 1 giga on the source needs 8 gigas on target. I think that the package create the table , populate it with bulk insert an then create indexes and constraint. Why such a difference of size ? Thanks a lot for help . |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.