SQL Server 2008 log file size is large and growing quickly

Question

Most of the time users will hit the database to read news. There are very few number of queries executed under transactions. 95% of the database hits would be for read-only purposes.

My database log files size is growing 1 GB per day. Even if I shrink the database, the log file size is not decreasing. What could be the reason for growing the log file size more and more? How can I control this? As per my knowledge log file does not increase when we read data from tables.

Any suggestions on how to deal with the log file growing? How can it be kept a manageable or reasonable size? Does this effect performance in any way?

Answer 1

Do you backup your database frequently? You need to perform full- and/or transaction log- backups in order for SQL Server to consider shrinking your log file.

Answer 2

Full backups will not help, you must regularly backup the transaction log (ad wll as the regular database full and fiiernetial backups)for it to be emptied. I fyou are not backing up the log and you are not in simple recovery mode, then yourtransactionlog has every transaction in it since the database was set up. If you have enough action that you are growing by a a gig a day, then you may also have large imports or updates affecting many records at once. It is possible you need to be in smiple recovery mode where transactions are not recorded individually. Do NOT do that however if you havea a mox of data from imports and users. In that case you need to back up the transaction log frequently to be able to keep the size manageble and to to a point in time. We backup opur transaction log every 15 minutes.

Read about transaction log backups in BOL to see how to fix the mess you have right now. Then get your backups set up and running properly. You need to read and understnd this stuff thoroughly before attempting to fix. Right now, you would probably be in a world of hurt if your server failed and you had to recover the database. Transaction log backups are critical to being able to recover properly from a failure.

Answer 3

There are couple of things to consider. What type of backups you do, and what type of backups you need. If you will have the answer to this question you can either switch Recovery Mode to simple or leave it full but then you need to make incremental backups on a daily basis (or whatever makes you happy with log size).

To set your database logging to simple (but only if you do Full Backups of your database!).

1. Right click on your database
2. Choose Properties
3. Choose Options
4. Set Recovery mode to simple

This will work and is best if your backup schedule is Full Backup every day. Because in such scenario your log won't be trimmed and it will skyrocket (just like in your case).

If you would be using Grandfather&Father&Son backup technique, which means Monthly Full backup, Weekly Full backup, and then every day incremental backup. Then for that you need Full Recovery Mode. If 1GB of log per day is still too much you can enable incremental backup per hour or per 15 minutes. This should fix the problem of log growing more and more.

If you run Full Backup every day you can switch it to simple recovery mode and you should be fine without risking your data (if you can leave with possible 1 day of data being lost). If you plan to use incremental then leave it at Recovery Mode Full.