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

Estimating suitable log file size (MSSQL Server)

Hi Experts.

I have an application that uses MS SQL Server. The application does not uses transactions explicitly but as I understand, applications create or use default transactions. The fact is, that the log file is growing and I need to reduce it. The client is asking me to recommend a minumum log file size suitable for the application but I have no idea how to calculate such size, based on how my program works. So my question would be:

How do you calculate the espace needed for an application, regarding the log size (MS SQL Server) based on the instructions you send to the database (inserts, updates, etc)?

Note: as I donĀ“t use explicit transactions that log information is useless for my application.

Thank you in advance.
  • 2
  • 2
2 Solutions
Transaction log is used to log all the statements that in any way will modify the status of the database. It is how SQL server works and any insert, update or delete, which are practically transactions, are first logged and then committed to the database if there are no errors. The transaction log could theoretically grow to any size it all depends what it is done.

If you have a database of lets say 10GB but you do an import from a file of 100GB the transaction log could practically go up to 100GB and in the end when committed the database will grow to 110GB and the transaction log will still be 100GB unless is shrunk.

So the estimate should start with the total size of the database itself, considering its future organic growth and then at least double that to make sure the log will have enough space when needed to grow. All depends as I said of the way database is used. If there are no huge import foreseen or some big temporary files as a result of intensive queries then the extra space can be limited but to start with double the size of the whole database is a common sense.

In regards to the transaction log size, even if it gets to 100GB as in the example I gave that doesn't mean that it will still necessarily grow in the future because as soon as the transactions are committed the space INSIDE the transaction log is reused in a circular mode. So it is possible that will never grow bigger than that.

If the size of 100GB for the log file is to much then it can only be shrunk to a minimum size that is needed for the transactions still in process. You can never shrink the log to 0. If the database is in simple recovery mode, which in production is not recommendable, then it will automatically shrink after transactions are committed. In full recovery mode there should be a periodically full backup scheduled, at least once a week, followed by differential backups more often, maybe daily, and transaction log backup done  hourly or even more often. If the transaction log gets too big can periodically shrunk with:

DBCC SHRINKFILE(log_file_name, 2)

The idea is that the log file grows only when it needs more space than is already allocated to it, basically when the free part of the log is smaller then the current transactions that are logged.

are you using some kind of data replication technology like log shipping or replication if not and you are not using log backups as well you can change your database to simple recovery model, and your log file will not grow any more.

arturosmAuthor Commented:

thanks for your responses. I know the client backup the database daily and the application does not uses the log data, never. For me, this log issue is just a secondary effect of using MS SQL Server. What would be the worst scenario of using simple recovery model?

Your database breaks one second before the daily backup. You lost all the transactions for the last 24 hr. That is pretty much unacceptable for almost all the cases.

A simple recovery is not recommendable to a production environment unless you have a case where you update the database periodically through some import or syncing process but even then is risky.  

There is no justification not to use full recovery as it is not at all a very complicated process. It can be set up through the Maintenance plan wizard in minutes.
arturosmAuthor Commented:
Thank you.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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