Solved

Do a BCP import without growing the T-Log???

Posted on 2003-11-01
3
305 Views
Last Modified: 2008-03-03
We do a BCP import into our database ever morning which causes a 250MB spike in our T-Logs.  The data that is getting imported is static, read-only data that is maintained on a separate server.  

Is there any way to prevent BCP from logging all of that traffic every morning so I won't have to back it up?  There is still other data hitting the database that is worth holding on to the T-logs, so I can't just turn T-logging off, or just delete the logs.

0
Comment
Question by:zclobes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9663419
Search for "logged bulk copy" in the MS SQL SERVER HELP FILE and you will see some useful info. Also see the link on that page "Optimizing Bulk Copy Performance".

It seems that logged cannot be completely turned off. But I am not sure.


Logged and Minimally Logged Bulk Copy Operations
When using the full recovery model, all row-insert operations performed by bcp are logged in the transaction log. For large data loads, this can cause the transaction log to fill rapidly. To help prevent the transaction log from running out of space, a minimally logged bulk copy can be performed if all of these conditions are met:

The recovery model is simple or bulk-logged.


The target table is not being replicated.


The target table does not have any triggers.


The target table has either 0 rows or no indexes.


The TABLOCK hint is specified. For more information, see Controlling the Locking Behavior.
Any bulk copy into an instance of Microsoft® SQL Server™ that does not meet these conditions is logged.

Before doing bulk copy operations, it is recommended that you set the recovery model to bulk-logged if you usually use full recovery. This will prevent the bulk copy operations from using excessive log space and possibly filling the log. However, even with bulk-logged recovery, some transaction log space will be used. You may want to create transaction log backups during the bulk copy operation to free up transaction log space.

When bulk copying a large number of rows into a table with indexes, it can be faster to drop all the indexes, perform the bulk copy, and re-create the indexes. For more information, see Optimizing Bulk Copy Performance.



Note  Although data insertions are not logged in the transaction log when a minimally logged bulk copy is performed, SQL Server still logs extent allocations each time a new extent is allocated to the table.

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 125 total points
ID: 9665008
Hi zclobes,

have you considered importing/loading into a
separate "temporary table database" which youd don't "log"

and then  
running sql to actually determine if any data changes are present
and just performing the necessary updates...

should reduce your logging requirements if the data changes infrequently...

also
transactional replication may be an answer if again only infrequent changes are taking place...
(and you can live with near time updates..)

other scenarios would be possible..

 

Cheers!
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VMware PVSCSI SQL Server 2016 AlwaysOn 2 37
Report 8 27
Datatable / Dates ? 4 33
Need SQL Query improved for multiple column joins 2 15
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question