Question :
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.
Answer :
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
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
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.
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.