0

My friend works in a company (pretty small in size) that is running SagePro ERP which has a SQL server 2005 database. Now the problem is, the database size (over the period of 7 years) has increased to 9 GB which is nothing really but the log file of the database is increasing drastically day by day and has now reached 124 GB.

He is worried that eventually their server might run out of storage if the log size keeps increasing. Upon googling, I found the "shrinkfile" method.

The question is why is the log file keeps increasing? How do I stop it? is "ShrinkFile" method the best method (I believe its not a permanent solution)?

P.S. They daily take full back-up of database.

Improve this question
5
  • Interesting question, but there can be many reasons. Rebuilding indexes uses transaction log, so check if SQL server has a job which frequently rebuild indexes. Also, there is another operations that use transaction log. Give us more details about server and database
    – veljasije
    Apr 16, 2014 at 6:51
  • Also, full-backup doesn't shrink transaction log
    – veljasije
    Apr 16, 2014 at 6:52
  • 1
    Are they doing transaction log backups? And at what interval?
    – Alex
    Apr 16, 2014 at 7:03
  • @veljasije Full backup doesn't shrink the log, but it does free pages in the existing log for reuse. If you're backing up your transaction logs consistently and see a consistent workload, your log files will not increase in size very often.
    – Bacon Bits
    Apr 16, 2014 at 15:48
  • @Alex Sorry for the late response. Yes, they are taking backups of transaction log. The interval was 4 hours. I told them to take backup after every 15 minutes. Is that okay?
    – NewbieProgrammer
    Apr 17, 2014 at 5:19

4 Answers 4

Reset to default
0

Probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default" recovery mode of Full and have not taken a log backup (or aren't taking them frequently enough).

refer this link, hope it will help you

Improve this answer
1
  • The database is linked to ERP system which records daily transactions, invoices etc so yeah I guess there is a lot of queries going on. Yes, the default recovery mode is set to Full. However, they were taking transaction log backup after every 4 hours. I will look into the link you provided. Thank you for your answer.
    – NewbieProgrammer
    Apr 17, 2014 at 5:24
0

There are two options regarding the transaction log files in SQL Server: Autogrowth and Maxsize. Autogrowth configure the size by which the log will grow when it's full and Maxsize is the the limit where it stops to grow. It seems that you don't have any limit set for the database.

In addition the log file size depends on the recovery model you have selected. You could further investigate this here :Selecting a Recovery Model

Improve this answer
3
  • Yes, I do know about Autogrowth and Maxsize. If I set a limit, won't it affect my database? Still I don't understand why the log size is above 120 GB
    – NewbieProgrammer
    Apr 16, 2014 at 7:16
  • You should truncatethe transaction log file each time you perform a backup to prevent the log file from reaching such size.Then you should choose a limit that would fit the usual transactions size between consecutive backups in order to avoid any performance penalties from the limit. I do not know your system in order to propose a limit but i would be amazed if a 10GB limit wouldn't suffice.
    – kadim
    Apr 16, 2014 at 7:31
  • I see, Thank you for your answer buddy. Much appreciated.
    – NewbieProgrammer
    Apr 17, 2014 at 5:22
0

Reasons for transaction log growth can be various: rebuilding indexes, other administrative tasks, some import data operations and so on.

You must first inspect your database, and search for administrative tasks that affect on transaction log. Also, full backup doesn't shrink transaction log, so you must do another administrative task to free space from transaction log.

"Shrink file" or "shrink database" are good tool when you don't have another option, but you must realize that operation is really tough in sense of resources.

You may inspect "Simple recovery model" which can be efficient if you don'e need transaction log. Simple model keep only active transaction in log file, while other inactive transactions are removed from log. this fact keep your log as small as possible.

Also, if you import big amount of data, while full recovery model is set, then every single row is logged as full transaction. Imagine 10000 rows in import process, that is 10000 records in transaction log.

Improve this answer
1
  • I see your point. However shouldn't there be a Garbage Collector (like in C#) that cleans up the transaction log so that all the old transaction log is cleaned after a sufficient period of time has passed?
    – NewbieProgrammer
    Apr 17, 2014 at 5:27
0

It's a very common problem with different reasons. I strongly suggest that you read these answers by Mike and Aaron -- Why Does the Transaction Log Keep Growing or Run Out of Space?

I don't want repeat what they explain very well in the answers.

Improve this answer
0

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