SQL Server data copy problem

Hi,



I am facing a great problem. I have a DB about 80GB. There are 217489811 number of records which include 2005, 2006 and 2007 data in 1 table. Now I want to split the table into by quarter tables 2005Q1, 2005Q2.....etc. It is because it is easier for me to housekeep the old data. (It takes about few hours to housekeep 3 month data currently, so that it affect my server performance a lot)



so I start my work, however it is failed every times. Actually, if I copy 3 month data to a new tables, it is nearly about 30 million records which needs about 1 GB space. However, it takes about 8 hours to complete the job and failed because of not enough space allocated. Actually, my Server has about 50GB space, but when the job complete and fail, all the space is occupied. But i wonder why this happen because the data only need about 1 GB, how come it use 50GB?



it seems that nothing i can do to housekeep the old data. because housekeep the current table takes too long time while splitting tables use up all the space. So how can i do?



For your information, the table has non-clustered index on the data date field



need your help

thanks,

Linus

Looks like your log is getting filled up. Look into using the BCP utility. Its much faster. Also, have a job running to TRUNCATE the LOG and let it run every minute or so, so the log doesnt fill up.

***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

my DB recovery mode is set to simple, so how comes the log will grow up?

BTW,

1. BCP utility means everything is copied to text file and then re-insert into SQL Server ?
2. how to write a job to truncate the log, can you show me some example?

Million thanks,
Linus

Even your database recovery is in simple, to transfer the data sql has to use log and if you are running in single transaction then your will not be truncated until it committs...

You can use BCP utility as mentioned and you are correct you have to copy the data to file and upload again or

You can use DTS package to copy the data and make sure your destination table do not have any indexes and you check the check box fast load option in dts properties to use log minimally...


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

yes BCP OUT is copying to text file and using BCP IN to the new db/table. It is much much faster than DTS and you can see the difference if the data is over a million rows.
For truncating the log i think the command is something like:

BACKUP LOG <Db> WITH TRUNCATE ONLY

I could be off a little you can check BOL for exact syntax. Now you can create a job with this command and schedule it to run every minute during your data transfer. Make sure the table has no indexes/FK's. Just have the clustered index and you should be fine.

***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

It depends on configuration of dts package...
Within the storage engine, the T-SQL BULK INSERT statement, bcp, and the DTS Bulk Insert task all execute the same code.

Showdown-bcp vs. DTS
http://www.sqlmag.com/Article/ArticleID/19760/sql_server_19760.html

DTS Optimization Tips
http://www.mssqlcity.com/Tips/dts_optimization.htm


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while writing BCP data-file


do you know what is the problem?

it seems that the performance does not boost up a lot.............it takes around 3 hours to import 15M data into the table...............however, there are 5M data remaining......i dont know when the job finish.....

are you creatig one gigantic file with all 15mil rows? Its better to create multiple smaller files than one big file.

***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

Do you have indexes on destination table? If yes, try without indexes...


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

It is not always the case...

MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

I have copied many times 15 million rows table under 60 minutes...
It all depends on the table structure, if the table has the text columns definately it is going to take long time even with bulk insert...

If the destination table has the index, then it will use transaction which cost the peformance...for fast BCP table should not have the index...



MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

thanks for you advice.............finally it takes 3 hours to complete the job...........maybe the destination table has index.......i forget to drop it first..............but just want to know why the index cost so much time?

another question..........how to delete data much more efficiency from this 217489811 rows table

It depends on how much data you want to delete out of 217 mil. rows...

How many rows you want to delete?


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

i think about 1.2M

WHILE 1 = 1
BEGIN
SET ROWCOUNT 100 -- you can try different values...
DELETE FROM TABLE WHERE ....
IF @@ROWCOUNT = 0 BREAK
-- If you want you can use WAITFOR DELAY cammand..to wait few seconds/ms between each iteration...
END


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

hi ,

i also want to know why this bcp action takes so much time for inserting the last few thousand records?

it stops at

1000 rows sent to SQL Server. Total sent: 597000 for about 45 mins.....but the whole job last for only 1 hr 17mins..........i dont understand about that

there are about 632464 rows copied.

Because it takes time to commit the transaction....if don't specify -b switch all rows will be treated as single transaction....

-b batch_size

Specifies the number of rows per batch of data copied. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch. By default, all data in the specified data file is copied in one batch. Do not use in conjunction with the -h "ROWS_PER_BATCH = bb" option.

Did you try with no indexes on destination table?

MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

is it meant that i should use -b to boost up the performance?

Now the destination table is with no index