You are viewing limited content. For full access, please sign in.

Question

Question

What should be the percentage of allocated size for a transaction log?

asked on July 18, 2014


I have a database that is used by 150 user in average. I have a backup plan that produced several differential and full. Everything was fine until recently. But in recent days, the transaction log seems to be taking more and more space.It grown up to take 15gb. I just want to know if is normal that it takes so much space? Otherwise, what would be the size of the transaction log for a database of 2gb?

3 0

Replies

replied on July 21, 2014 Show version history

It is normal for a transaction log to grow, especially if you have that many users accessing it and that much data stored in the database, however 15GB is rather large and you probably want that space back.

It all depends on what is more important to you, how well the SQL Server database is backed up or how much space the transaction log is allowed to use. You obviously have a good backup plan in place, so the real question is, can you afford to lose data up to your last backup? If you're backing up every night, the real question is, can you afford to lose a day's worth of data? If you answered no, leave the transaction log the way it is. If you answered, yes, change the transaction log's recovery type from Full to Simple. This change will instantly shrink your transaction log file to just a few megabytes, and you won't see the file grow to 15GB again.

EDIT: To answer your original question, there's no set ratio of how big a database should be in relation to it's transaction log. The transaction log just keeps a record of everything that's gone on with the database for backup/restoration purposes. In short, interaction with the DB grows the transaction log.

0 0
replied on July 21, 2014

After review of all the log of the server, i have noticed what can be the cause of my problem. For example, our log normally is 200 MB and it varies a few kb per hour, but when we delete one entry that is a "document" the log is filled up to the maximum allowed size in 1-3 minutes. He moves from 200 MB to 10 GB. It seems that there is also a routine ("deleteOldRecycleBinFiles") that would review all entry in the recycle bin and remove all documents that have reached a certain age. This could be the trigger element of our problem, because we never had to delete documents from the recycle bin before. I dont know if this feature is new from the version 9 of LaserFiche but it has start to occur 6 mounth after his installation. I just want to know if this is a known problem and if there is any solution.

2 0
You are not allowed to follow up in this post.

Sign in to reply to this post.