Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a large database, with a size of approx 400GB. We are using bulk insert (from upstream system) with the following parameters:

`Driver=/IIS/Appl/Server/branded_odbc/lib/VMsqls24.so`
`Description=DataDirect 6.0 SQL Server Native Wire Protocol`
`Address=SQLLPB2GBRxxx.xxxxx.COM,14331`
`AnsiNPW=Yes`
`AuthenticationMethod=4`
`Database=DB_PROD`
`QuotedId=No`
`EnableBulkLoad=1`
`BulkBinaryThreshold=32`
`BulkCharacterThreshold=-1`
`BulkLoadBatchSize=1024`
`BulkLoadOptions=0`

The delta changes in the DB represent approx 70GB at month end. The transaction log file is falling over at 800GB (twice the size of the actual DB, where 800GB is the current size of the allocated transaction log drive). Autogrow is enabled on the transaction log, but we are unsure WHY it is becoming so large. Simple recovery is set.

Are there any parameters (either upstream on the bulk insert, or on the transaction log db ) we can fine tune?

share|improve this question
1  
The checkpoint process can only reuse the log (in simple recovery mode) to the point of the oldest active transaction. It sounds like your bulk operation is potentially one gigantic transaction. –  Thomas Stringer Aug 20 '12 at 13:22
    
Are you BULK INSERTing into a staging table, or directly into the target table? –  Jon Seigel Aug 20 '12 at 13:31
    
The transactions are loaded in (up to) 1024 rows each, so don't think it would be determined as ONE large transaction. The bulk insert loads directly into the target table. Would that massively bloat the size of the transaction log? –  Hamish Tonkin Aug 20 '12 at 14:14
    
Does your process include deleting all the data in all of the tables first, then bulk loading them? –  CleanFill Aug 20 '12 at 17:02
1  
What does your Log_Reuse_Wait show? Have you tried running that often when the log is growing really large to see what it's actually waiting on? This will be useful. –  Ali Razeghi Nov 1 '12 at 16:21
show 1 more comment

migrated from stackoverflow.com Aug 20 '12 at 13:24

This question came from our site for professional and enthusiast programmers.

2 Answers

One option is to set the recovery model to BULK LOGGED for the duration of the month end load. FULL recovery mode logs all changes to the transaction log as well as the previous values so rollback can happen so it's possible that more data gets written to the log than the data file.

BULK LOGGED stores the location of the page that was changed and then that page is copied into the transaction log backup. The transaction log itself will be smaller but you may notice an increase in the size of the tlog backups. It's important to be aware that you can't do a point in time restore from a tlog backup that has bulk logged operations.

If your recovery model is already BULK LOGGED or SIMPLE look at the log_reuse_wait_desc for the database to determine what you need to do to clear the log. select name, log_reuse_wait_desc from sys.databases If it's LOG_BACKUP you need to increase the frequency of log backups. If it's something else update the question and we'll look into it.

share|improve this answer
add comment

While it's running try running the CHECKPOINT command separately. It could be that your system is not getting to perform checkpoints and may need some extra prodding to do so.

share|improve this answer
    
Surely doing additional checkpoints will just increase the transaction log even further? –  Hamish Tonkin Aug 21 '12 at 10:25
2  
A checkpoint is the process which involves getting the information which is in the log file(s) into the data file(s). If the database is in the Simple recovery model, then a checkpoint allows the log to be cleared. –  Rob Farley Aug 21 '12 at 22:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.