• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

high usage ldf file of memory in simple recovery

My database is in the simple recovery mode. During bulk-operations it is growing very high and utilization of memory space is very high and we had a seperate drive for ldf files. After some time again it came to its original size.
Pl, let know why it is growing when it is in simple recovery? The cause might be SQL Server may not have enough time to run a checkpoint and complete the log truncation? Correct if I’m wrong?
Let me know the best practice to maintain the log file when it is in simple recovery?
1 Solution
So, there is really nothing different between simple and full, other than simple will not fully log some bulk logged operations and it truncates the log on checkpoint.  If you've got long running transactions, then your log is likely not having an opportunity to truncate.
Máté FarkasDatabase Developer and AdministratorCommented:
You don't have to care about the size of the transaction log (.ldf file). At the optimal case, it is better at what bigger.
First, every modification in the database file is written to log. If the commit of the transaction is executed succesfully then the logged modifications will be copied to the data file. So if your transaction modifies large amount of pages (such as bulk insert) then the log file will grow but after the commit this large log file becomes empty (in simple recovery model) and the space within the file can be reusable (but the physical size will not decrease automatically).
If the log file is not enough large for the actual transaction the storage engine must increase the size of the log file. These space allocations can cause the high disk usage. You have to set the size and the decreasing size enough large and it is not a good idea to truncate and shrink it.
krishna_harikAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now