We're consolidating data from a bunch of databases into four reporting databases each night.

Because the entire dataset is imported each night we do not need to be able to restore the data to a point in time. Thus the databases are in simple recovery mode.

Each time we run the import however, our database ldf files grow to absurdly large sizes (50+ Gigs).

Is there a way to turn off the logging altogether or get SQL Server to clear those log files sooner?

I'm guessing no for clearing as the log_reuse_wait_desc is ACTIVE_TRANSACTION.

  • What's the import mechanism or tool? – Mark Storey-Smith May 6 '13 at 18:18
  • its a stored procedure that pulls data from a linked server access database and inserts it into a sql server database – Lumpy May 6 '13 at 18:24
  • 1
    Break it up into batches... as per Remus's answer (which appeared as I was typing). – Mark Storey-Smith May 6 '13 at 18:26

You can import the data by using operations that can be minimally logged. See Operations That Can Be Minimally Logged and Prerequisites for Minimal Logging in Bulk Import.

And you should consider reducing the duration of transactions during the import. Use batches of limited size and commit periodically.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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