bcp file size

I use bcp to copy a file and the bak it generated is 966637 KB. However, when I run sp_spaceused, it shows me the following size :

reserved data index_size unused
------ ------ ------ ------
550752 KB 395272 KB 155464 KB 16 KB

The difference doesn't make sense to me. Can anyone help pls?What file are you using to copy with bcp? mdf?|||Did you bcp the data out with the -c or -n switch? This is quite understandable with the -c switch, as all the numeric and datetime values will take up much more space than in the database.|||I use the bcp to copy 1 table and the command I use is :

bcp <table_name> out <output file> -N - S <server Name> -k -T -m 1 -e <error file>|||So, you are trying to correlate the size of the table to the size of the resulting file using BCP? Well, it shouldn't make sense, otherwise, what's the point of having a database, heh?!|||So, you are trying to correlate the size of the table to the size of the resulting file using BCP? Well, it shouldn't make sense, otherwise, what's the point of having a database, heh?! Atomicity, Consistency, Isolation, and Durability... just to name a couple.

bcp - Transaction LogFile Size issue

Hi Experts,
I am having new issue again,
My task is to copy data from one table to another table residing in
different database.
Table happenes to be extreamly large. (Contains around 15 million
rows.)
I tried several ways (SQL query, SSIS packages,etc...)
I found BCP utility suits my requirement. So planned for BCP.
I am trying following ways (Two steps)
1. bcp <MyFirstDB.TableName> out <MyFlatFilePath> -n -T
2.bcp <MySecondDB.TableName> in <MyFlatFilePath> -n -T
(Basically, Copying data from source to flat file and from there to
DestinationTable.)
Here problem is, My transaction Log file (MySecondDB_log.ldf) grows
like hell on second command. It grows upto 8 GB (max. free space That
I have on disk ).
My datatransfer will be incomplete because of no space on HardDisk.
PLease let me know, where I am going wrong,if you have better method,
how can i optimize my data transfer. (My log file grows by 2 MB, not
with %ge)
Thank you in advance,
Sriharsha Karagodu.
Are you changing the second database's recovery to bulk?
On Mar 17, 9:56Xam, sriharsha.karag...@.gmail.com wrote:
> Hi Experts,
> I am having new issue again,
> My task is to copy data from one table to another table residing in
> different database.
> Table happenes to be extreamly large. (Contains around 15 million
> rows.)
> I tried several ways (SQL query, SSIS packages,etc...)
> I found BCP utility suits my requirement. So planned for BCP.
> I am trying following ways (Two steps)
> 1. bcp X<MyFirstDB.TableName> out <MyFlatFilePath> -n -T
> 2.bcp X<MySecondDB.TableName> in <MyFlatFilePath> -n -T
> (Basically, Copying data from source to flat file and from there to
> DestinationTable.)
> Here problem is, My transaction Log file (MySecondDB_log.ldf) grows
> like hell on second command. It grows upto 8 GB (max. free space That
> I have on disk ).
> My datatransfer will be incomplete because of no space on HardDisk.
> PLease let me know, where I am going wrong,if you have better method,
> how can i optimize my data transfer. (My log file grows by 2 MB, not
> with %ge)
> Thank you in advance,
> Sriharsha Karagodu.
|||On Mar 17, 6:59Xpm, Sean <ColdFusion...@.gmail.com> wrote:
> Are you changing the second database's recovery to bulk?
> On Mar 17, 9:56Xam, sriharsha.karag...@.gmail.com wrote:
>
>
>
>
>
>
> - Show quoted text -
Actually I read about Changing the Recovery Property. But Where Do I
Get that option?
When I Do Property of Database--> options-->recovery, This will have
three options like,
TonrnPageDetection,
CheckSum,
None.
So, Not sure where will get option to change the recovery to BULK.
please guide me.
|||You can do it visually, but the syntax goes like this:
ALTER DATABASE [database name]
SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]
Also, to see what the current recovery model is run: SP_HELPDB
[database name]
1. Run SP_HELPDB [database name], and note the model used.
2. ALTER DATABASE [database name] SET RECOVERY BULK_LOGGED
3. Run SP_HELPDB [database name] to double check the settings
4. Run bcp import
5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
6. SP_HELPDB [database name] to double check
|||Shriharsha,
When BCPing in so much data, it is also good to use the batch size operator
to control the transaction size. Such as:
bcp ... -b 50000
This will break up your bcp into about 300 batches, which will speed it up
and gives you more transaction log control. You could then (if necessary)
run extra BACKUP LOGs during the bcp in.
RLF
"Sean" <ColdFusion244@.gmail.com> wrote in message
news:ba587a67-ef18-452e-88a2-d6132234fc5c@.t54g2000hsg.googlegroups.com...
> You can do it visually, but the syntax goes like this:
> ALTER DATABASE [database name]
> SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]
> Also, to see what the current recovery model is run: SP_HELPDB
> [database name]
> 1. Run SP_HELPDB [database name], and note the model used.
> 2. ALTER DATABASE [database name] SET RECOVERY BULK_LOGGED
> 3. Run SP_HELPDB [database name] to double check the settings
> 4. Run bcp import
> 5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
> 6. SP_HELPDB [database name] to double check
>
|||Thanks Rusell and Sean,
I have changed the model to "BULK_LOGGED" and used -b attribute in bcp import.
Smaller the batch size, faster is the data transfer
"Russell Fields" wrote:

> Shriharsha,
> When BCPing in so much data, it is also good to use the batch size operator
> to control the transaction size. Such as:
> bcp ... -b 50000
> This will break up your bcp into about 300 batches, which will speed it up
> and gives you more transaction log control. You could then (if necessary)
> run extra BACKUP LOGs during the bcp in.
> RLF
> "Sean" <ColdFusion244@.gmail.com> wrote in message
> news:ba587a67-ef18-452e-88a2-d6132234fc5c@.t54g2000hsg.googlegroups.com...
>
>

bcp - Transaction LogFile Size issue

Hi Experts,
I am having new issue again,
My task is to copy data from one table to another table residing in
different database.
Table happenes to be extreamly large. (Contains around 15 million
rows.)
I tried several ways (SQL query, SSIS packages,etc...)
I found BCP utility suits my requirement. So planned for BCP.
I am trying following ways (Two steps)
1. bcp <MyFirstDB.TableName> out <MyFlatFilePath> -n -T
2.bcp <MySecondDB.TableName> in <MyFlatFilePath> -n -T
(Basically, Copying data from source to flat file and from there to
DestinationTable.)
Here problem is, My transaction Log file (MySecondDB_log.ldf) grows
like hell on second command. It grows upto 8 GB (max. free space That
I have on disk :) ).
My datatransfer will be incomplete because of no space on HardDisk.
PLease let me know, where I am going wrong,if you have better method,
how can i optimize my data transfer. (My log file grows by 2 MB, not
with %ge)
Thank you in advance,
Sriharsha Karagodu.Are you changing the second database's recovery to bulk?
On Mar 17, 9:56=A0am, sriharsha.karag...@.gmail.com wrote:
> Hi Experts,
> I am having new issue again,
> My task is to copy data from one table to another table residing in
> different database.
> Table happenes to be extreamly large. (Contains around 15 million
> rows.)
> I tried several ways (SQL query, SSIS packages,etc...)
> I found BCP utility suits my requirement. So planned for BCP.
> I am trying following ways (Two steps)
> 1. bcp =A0<MyFirstDB.TableName> out <MyFlatFilePath> -n -T
> 2.bcp =A0<MySecondDB.TableName> in <MyFlatFilePath> -n -T
> (Basically, Copying data from source to flat file and from there to
> DestinationTable.)
> Here problem is, My transaction Log file (MySecondDB_log.ldf) grows
> like hell on second command. It grows upto 8 GB (max. free space That
> I have on disk :) ).
> My datatransfer will be incomplete because of no space on HardDisk.
> PLease let me know, where I am going wrong,if you have better method,
> how can i optimize my data transfer. (My log file grows by 2 MB, not
> with %ge)
> Thank you in advance,
> Sriharsha Karagodu.|||On Mar 17, 6:59=A0pm, Sean <ColdFusion...@.gmail.com> wrote:
> Are you changing the second database's recovery to bulk?
> On Mar 17, 9:56=A0am, sriharsha.karag...@.gmail.com wrote:
>
> > Hi Experts,
> > I am having new issue again,
> > My task is to copy data from one table to another table residing in
> > different database.
> > Table happenes to be extreamly large. (Contains around 15 million
> > rows.)
> > I tried several ways (SQL query, SSIS packages,etc...)
> > I found BCP utility suits my requirement. So planned for BCP.
> > I am trying following ways (Two steps)
> > 1. bcp =A0<MyFirstDB.TableName> out <MyFlatFilePath> -n -T
> > 2.bcp =A0<MySecondDB.TableName> in <MyFlatFilePath> -n -T
> > (Basically, Copying data from source to flat file and from there to
> > DestinationTable.)
> > Here problem is, My transaction Log file (MySecondDB_log.ldf) grows
> > like hell on second command. It grows upto 8 GB (max. free space That
> > I have on disk :) ).
> > My datatransfer will be incomplete because of no space on HardDisk.
> > PLease let me know, where I am going wrong,if you have better method,
> > how can i optimize my data transfer. (My log file grows by 2 MB, not
> > with %ge)
> > Thank you in advance,
> > Sriharsha Karagodu.- Hide quoted text -
> - Show quoted text -
Actually I read about Changing the Recovery Property. But Where Do I
Get that option?
When I Do Property of Database--> options-->recovery, This will have
three options like,
TonrnPageDetection,
CheckSum,
None.
So, Not sure where will get option to change the recovery to BULK.
please guide me.|||You can do it visually, but the syntax goes like this:
ALTER DATABASE [database name]
SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]
Also, to see what the current recovery model is run: SP_HELPDB
[database name]
1. Run SP_HELPDB [database name], and note the model used.
2. ALTER DATABASE [database name] SET RECOVERY BULK_LOGGED
3. Run SP_HELPDB [database name] to double check the settings
4. Run bcp import
5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
6. SP_HELPDB [database name] to double check|||Shriharsha,
When BCPing in so much data, it is also good to use the batch size operator
to control the transaction size. Such as:
bcp ... -b 50000
This will break up your bcp into about 300 batches, which will speed it up
and gives you more transaction log control. You could then (if necessary)
run extra BACKUP LOGs during the bcp in.
RLF
"Sean" <ColdFusion244@.gmail.com> wrote in message
news:ba587a67-ef18-452e-88a2-d6132234fc5c@.t54g2000hsg.googlegroups.com...
> You can do it visually, but the syntax goes like this:
> ALTER DATABASE [database name]
> SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]
> Also, to see what the current recovery model is run: SP_HELPDB
> [database name]
> 1. Run SP_HELPDB [database name], and note the model used.
> 2. ALTER DATABASE [database name] SET RECOVERY BULK_LOGGED
> 3. Run SP_HELPDB [database name] to double check the settings
> 4. Run bcp import
> 5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
> 6. SP_HELPDB [database name] to double check
>|||Thanks Rusell and Sean,
I have changed the model to "BULK_LOGGED" and used -b attribute in bcp import.
Smaller the batch size, faster is the data transfer
"Russell Fields" wrote:
> Shriharsha,
> When BCPing in so much data, it is also good to use the batch size operator
> to control the transaction size. Such as:
> bcp ... -b 50000
> This will break up your bcp into about 300 batches, which will speed it up
> and gives you more transaction log control. You could then (if necessary)
> run extra BACKUP LOGs during the bcp in.
> RLF
> "Sean" <ColdFusion244@.gmail.com> wrote in message
> news:ba587a67-ef18-452e-88a2-d6132234fc5c@.t54g2000hsg.googlegroups.com...
> > You can do it visually, but the syntax goes like this:
> >
> > ALTER DATABASE [database name]
> > SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]
> >
> > Also, to see what the current recovery model is run: SP_HELPDB
> > [database name]
> >
> > 1. Run SP_HELPDB [database name], and note the model used.
> > 2. ALTER DATABASE [database name] SET RECOVERY BULK_LOGGED
> > 3. Run SP_HELPDB [database name] to double check the settings
> > 4. Run bcp import
> > 5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
> > 6. SP_HELPDB [database name] to double check
> >
>
>

BCP - Hint exceeds

Query hints exceed maximum command buffer size of 1023 bytes(1029 bytes input).
i tried a long query in BCP because i have to insert a character to one of its fields.
hope somebody had encountered and solved this (sql server 2000). thanks in advance..what's your bcp command line?|||here...

bcp "Select 'A'+Customer_Code, PFW_Customer_Code, Newsys_Customer_Code, SBT_Customer_Code, BM_Customer_Code,

Customer_Class_Code, Customer_Company_Name, Customer_Market_Name, Contact_Person_Name, Contact_Person_Position,

Contact_Person_Contact_Number, Contact_Person_Fax_Number, Contact_Person_Email_Address, Customer_Delivery_Address,

Customer_Market_Type_Code, Customer_Market_Cluster_Code, Customer_Level_Code, Customer_Location_Code,

Customer_Route_Name, Customer_CreditLimit, Customer_Status, Customer_Status_Decription, Customer_Auto_Approve,

Customer_Set_Code, Customer_Receipt_Type, Customer_Outstanding_Balance, Customer_Available_Balance,

Customer_Last_Order_Date, Customer_Last_Order_Amount, NewSys_Customer_Salesman_Code, OnHold_Status, OnHold_Date,

OnHold_Remarks, AR_CashAccountCode, AR_CashAccountDesciption, Create_User_Code, Create_Date, Modify_User_Code,

Modify_Date, Status, SpareField1, SpareField2, Remarks1, Remarks2, newsys_terms_fpm, newsys_terms_gp,

newsys_terms_ham from CDO_MAIN..GenMKT_Customer_Masterfile" out c:\GenMKT_Customer_Masterfile.txt -t, -f

c:\GenMKT_Customer_Masterfile.xml -S10.10.1.8 -Usa -Psa|||Perhaps you could make that into a view in SQL Server, and just bcp out the view?|||you should be using queryout, not out.|||wrong code, i already replaced it with queryout but still fails...thus view in sql server runs bcp?|||in that case I would do as mcrowley suggests. create a view, and then run this bcp:

bcp CDO_MAIN..YourView out c:\GenMKT_Customer_Masterfile.txt -t, -f c:\GenMKT_Customer_Masterfile.xml -S10.10.1.8 -Usa -Psa|||Thanks a lot MCrowley & jezemine!!!|||-Usa -Psa
But first you should change the sa-password, put it in a vault somewhere and only use it in emergencies... :angel:

Batch Size

Hi
I'm trying to improve a crawl's performance. Online books refers to
changing the default batch size from 1600 to a number associated with the
number of processors on my server.
For the life of me, I have no idea how to go about changing the batch size
for full-text search. Any and all pointers apprciated.
Rob
Adding to my post. I thought this was a 2005 specific newsgroup - sorry.
I'm running sql2005 Sept. CTP Standard Edition. on a 4 way box with
hyperthreading. fast XEON procesors (don't exactly remember how fast).
Table is 4000000 rows and growing, and taking a LONG time (8 hours) to
populate. I've done the other hardware things suggested by books online.
Thanks.
"Robert G." wrote:

> Hi
> I'm trying to improve a crawl's performance. Online books refers to
> changing the default batch size from 1600 to a number associated with the
> number of processors on my server.
> For the life of me, I have no idea how to go about changing the batch size
> for full-text search. Any and all pointers apprciated.
> Rob
|||Robert,
Not to worry, the private "SQL Server 2005" newsgroup has been taken down as
SQL Server 2005 RTM'ed today (10/27/05), so now this fulltext newsgroup is
officially both SQL Server 2000 FTS and SQL Server 2005 FTS (SQL2005FTS),
checkout my blog entry from today for some of the initial details: "SQL
Server 2005 has RTM'ed !!"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!552.entry
You're reading the SQL 2005 BOL title "Performance Tuning and Optimization
(Full-Text Search)"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/ef39ef1f-f0b7-4582-8e9c-31d4bd0ad35d.htm
Can I assume that you have already done the following?
1. Ensure the base table has a clustered index.
2. Place SQL [database] log (*.ldf), database files (*.mdf & *.ndf), and the
full-text catalog on separate disks.
Have you reviewed the MSFTESQL<$Instance_Name>:Service "Batches in ready
queue" perfmon counter values? I'm not sure what constitutes "low" for your
server, but could you reply back with the range of values you are seeing now
while the FT Indexing is ongoing?
FYI, the explain for this performance counter: "Number of batches in the
ready queue. This queue buffers work that will be given to the filter
daemons."
Thanks,
John
SQL 2005 Full Text Search
http://spaces.msn.com/members/jtkane/
"Robert G." <RobertG@.discussions.microsoft.com> wrote in message
news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...[vbcol=seagreen]
> Adding to my post. I thought this was a 2005 specific newsgroup - sorry.
> I'm running sql2005 Sept. CTP Standard Edition. on a 4 way box with
> hyperthreading. fast XEON procesors (don't exactly remember how fast).
> Table is 4000000 rows and growing, and taking a LONG time (8 hours) to
> populate. I've done the other hardware things suggested by books online.
> Thanks.
> "Robert G." wrote:
|||Robert,
Not to worry, the private "SQL Server 2005" newsgroup has been taken down as
SQL Server 2005 RTM'ed today (10/27/05), so now this fulltext newsgroup is
officially both SQL Server 2000 FTS and SQL Server 2005 FTS (SQL2005FTS),
checkout my blog entry from today for some of the initial details: "SQL
Server 2005 has RTM'ed !!"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!552.entry
You're reading the SQL 2005 BOL title "Performance Tuning and Optimization
(Full-Text Search)"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/ef39ef1f-f0b7-4582-8e9c-31d4bd0ad35d.htm
Can I assume that you have already done the following?
1. Ensure the base table has a clustered index.
2. Place SQL [database] log (*.ldf), database files (*.mdf & *.ndf), and the
full-text catalog on separate disks.
Have you reviewed the MSFTESQL<$Instance_Name>:Service "Batches in ready
queue" perfmon counter values? I'm not sure what constitutes "low" for your
server, but could you reply back with the range of values you are seeing now
while the FT Indexing is ongoing?
FYI, the explain for this performance counter: "Number of batches in the
ready queue. This queue buffers work that will be given to the filter
daemons."
Thanks,
John
SQL 2005 Full Text Search
http://spaces.msn.com/members/jtkane/
"Robert G." <RobertG@.discussions.microsoft.com> wrote in message
news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...[vbcol=seagreen]
> Adding to my post. I thought this was a 2005 specific newsgroup - sorry.
> I'm running sql2005 Sept. CTP Standard Edition. on a 4 way box with
> hyperthreading. fast XEON procesors (don't exactly remember how fast).
> Table is 4000000 rows and growing, and taking a LONG time (8 hours) to
> populate. I've done the other hardware things suggested by books online.
> Thanks.
> "Robert G." wrote:
|||Hi
Your assumption is correct. Clustered index, files spread over 3 logical
disks (and two channels, for good measure).
Low is 1. According to that article, I should be seeing batches in the 4 -
8 range (maybe 16, since SQL believes I have 8 processors (hyperthreading)).
Low CPU is 0 - 5%, sometimes peaking at 30%, but rarely.
The article states "if the number of batches is low ... Increase full-text
batch size". That's what I'm trying to figure out.
They even give a suggested range - "default is 1600 rows per batch. For an
8-way computer 700Mhz CPU, the batch size recommend is 5000 rows."
Sounds like a great configuration change, if I could figure out how to
change the configuration!
Thanks again.
Rob
to quote the article:
"John Kane" wrote:

> Robert,
> Not to worry, the private "SQL Server 2005" newsgroup has been taken down as
> SQL Server 2005 RTM'ed today (10/27/05), so now this fulltext newsgroup is
> officially both SQL Server 2000 FTS and SQL Server 2005 FTS (SQL2005FTS),
> checkout my blog entry from today for some of the initial details: "SQL
> Server 2005 has RTM'ed !!"
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!552.entry
> You're reading the SQL 2005 BOL title "Performance Tuning and Optimization
> (Full-Text Search)"
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/ef39ef1f-f0b7-4582-8e9c-31d4bd0ad35d.htm
> Can I assume that you have already done the following?
> 1. Ensure the base table has a clustered index.
> 2. Place SQL [database] log (*.ldf), database files (*.mdf & *.ndf), and the
> full-text catalog on separate disks.
> Have you reviewed the MSFTESQL<$Instance_Name>:Service "Batches in ready
> queue" perfmon counter values? I'm not sure what constitutes "low" for your
> server, but could you reply back with the range of values you are seeing now
> while the FT Indexing is ongoing?
> FYI, the explain for this performance counter: "Number of batches in the
> ready queue. This queue buffers work that will be given to the filter
> daemons."
> Thanks,
> John
> --
> SQL 2005 Full Text Search
> http://spaces.msn.com/members/jtkane/
>
> "Robert G." <RobertG@.discussions.microsoft.com> wrote in message
> news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...
>
>