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.

This question already has an answer here:

I have a database that is set to Simple recovery mode. There is 99% free space in the log file and I can manually truncate the log file right down to 500 MB.

The next day, after a daily import process runs, the log file will be back up to ~70GB.

If I check log_resuse_wait in sys.databases it returns 0 which would indicate nothing is blocking a shrink operation.

Why isn't the log file naturally shrinking? Also, how can I find what SQL queries are causing the log file to grow to such a size?

share|improve this question

marked as duplicate by dezso, ypercube, Thomas Stringer, Aaron Bertrand, Jon Seigel Feb 12 '13 at 15:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Damn it - cannot believe I couldn't find that! –  Barry Feb 12 '13 at 11:38

2 Answers 2

You must check log_reuse_wait during the import process. Most likely you have a some long running transaction started at the beginning of the import that is pinning the log for the entire import duration. Simple Recovery model log is automatically truncated, not shrunk. See How to Shrink the SQL Server log.

share|improve this answer

Have a look at this post: http://blog.sqltechie.com/2011/02/dmvs-instead-of-dbcc-opentran.html

  1. Modify the query to suite your needs and store output in a table.
  2. Schedule an agent job to run the above (say every 5 min)

Hopefully, when you check the table (after some time), you should be able to see the database size and at what point it started to grow with the relevant message indicating the problem?

I suspect it's the 'size of your transaction / import process' - "...the log cannot truncate on checkpoint for any of the changes that are still in open transactions or that have started since that transaction started." Is there scope to break this import process into smaller chunks?

I hope that helps.

share|improve this answer

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