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'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.

share|improve this question
    
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

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer

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.