5

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?

Thomas Stringer
  • 42,434
  • 9
  • 120
  • 155
Hamish Tonkin
  • 51
  • 2

2 Answers2

1

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.

Rob Farley
  • 16,324
  • 2
  • 39
  • 61
0

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.

cfradenburg
  • 678
  • 3
  • 7