Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-01-13 : 07:59:43
|
| Kevin writes "c:\progra~1\micros~1\80\tools\binn\bcp.exe cis_rep..eis_tran_drill in E:\vendor_apps\cdms\cdms_jobs\data\eis_tran_drill_3_vw.dat /e E:\vendor_apps\cdms\cdms_jobs\err\eis_tran_drill3.err /c /t\t /r\n /b 20000 /S misdev06 /U CDMSAdmin /P cdmsadmin > E:\vendor_apps\cdms\cdms_jobs\log\eis_tran_drill3.logCommand above halts after loading 15.9 or 9.6 million rows when run as part of a larger job. Command screen remains open, and any keyboard input will cause it to resume. Job runs on source server. Both servers run SQL2000 (8.00.76). This code has been running successfully on various tables in production since before version 6.5. The problem was experienced when we used the code on this table in the UAT server.When the job stops, the log file ends with 20000 rows sent to SQL Server. Total sent: 964000020000 rows sent to SQL ServerThe error file is empty. If we run this portion of the job by itself, it works. If we run it a the end of a multi-table extract and load operation, it stops." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-13 : 08:07:11
|
| bcp will appear to pause when it hits the end of a large import file. This can happen when bcp'ing into a table with indexes (which get rebuilt) or when the database is not set to do bulk operations. Sometimes the data is actually imported into tempdb, and then inserted into the actual table. Check the recovery model on your UAT database and see if it is set to Full Recovery. You may want to change it to Simple or Bulk-Logged.While this behavior is normal under the circumstances, you can eliminate it by setting the recovery model correctly, and dropping all non-clustered indexes from the table. Books Online has more details on optimizing bcp performance, including these options and others. Also look at the TABLOCK and ORDER hints. |
 |
|
|
kkeppler
Starting Member
2 Posts |
Posted - 2005-01-24 : 15:43:43
|
| robvolk,Thank you for the hints. There are some I have not tried, and I will post the solution that works.KevinKevin |
|
|
|
kkeppler
Starting Member
2 Posts |
Posted - 2005-01-24 : 16:30:33
|
| robvolk,I did some homework on your email, and I would appreciate any further thoughts you might have. Your response is in italics: bcp will appear to pause when it hits the end of a large import file.When it stops at 15.96M, it has about 5M rows to go.This can happen when bcp'ing into a table with indexes...We have tried this with and without indexes on the target.Check the recovery model on your UAT database... The source (UAT) is SIMPLE and the target is BULK_LOGGED.Also look at the TABLOCK and ORDER hints.We have tried it with and without the TABLOCK hint. Thanks for pointing out the ORDER hint. I will use it if I decide to leave the indexes up during bulk load, but I am trying to meet the conditions for minimal logging, and I will probably drop and create the indexes.Kevin |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-24 : 19:33:09
|
| You should monitor the database you are bcp'ing into, and also tempdb. Check them periodically for transaction log usage, and possible file growth. A large import may fill the log and force an autogrow...that sounds very likely for the pause you described. It could also be a file grow on the database file(s). You can watch them in Enterprise Manager, the Taskpad view is a great way to do it. Simply refresh the taskpad view periodically for tempdb and the other database. If you see tempdb's allocation percentage growing, or a lot of transaction log activity, then bcp is NOT doing a direct bulk insert operation. If the operation is going efficiently, you should see no activity in tempdb, a little bit of activity in the database log, and more allocation in the database file. A little bit of log growth is normal, these are new page allocations for the inserted data. Data growth should be higher and represents the actual data being inserted. BTW, if you use the ORDER hint, make SURE that the file is indeed ordered correctly, otherwise bcp will throw an error at the very end, even though it says it's importing.I forgot to mention Gert Draper's site:http://www.sqldev.net/He has a number of articles on optimizing bcp and BULK INSERT, try to find his PowerPoint presentations too. Definitely worth the time. |
|
|
|
|
|
|
|
|
BCP IN in halts without generating error.