SQL Server transaction log files

Hi List,
our database is in simple mode, but still our transaction log files keep growing and growing... I have to shrink the files manually or else they would keep growing. Is there a way to prevent the file from growing? does everyone have to shrink those files manually? How big do your transaction log files get?

Thanks in advance for any input/opinions


E810, TR 8.96.1.4, SQL2005, win2k3, WAS6.0

Wadie,

This sounds to me like a dangerous situation. Your database in Simple means that you cannot restore transactions that have happened after the last full backup.

You need to look at changing the database to Full Recovery and scheduling transaction log backups throughout the day.

You might look for a good SQL Server book or search articles on microsoft.com and learn about the options for database backups.

Our exaxmple:
Our database files are on the I: partition. Log files are on the D: partition. We run transaction log backups, using SQL maintenance plan and scheduled job, every half hour during the day. Typically, the backups are around 100 MB. The backup job writes them to the G: drive which is on a separate server. The backup job then clears the backed up transactions out of the log.

Our physical log file on the D: drive is over 3 GB since we don't run log backups during the night and the batch processes create around 3 GB of logs, but the file does not grow continuously beyond that amount.

P.S. I am not yet a talented DBA, so there may be much better options out there. Our DBA left the company recently, but this is the setup he had and it works for us.

Jer

I don't have to do this all the time, so I warn you in advance that there is a limit to how much I can help. I also apologize if any of the below is already known to you.

You want the transaction logs to reuse existing space when it is done using it, rather than keep growing. In simple mode, SQL Server won't reuse the space until you truncate the log, and it won't truncate the log until you hit a checkpoint.

Now you should automatically be creating a checkpoint when you back up your database, and thus automatically truncating the log. Assuming you back up every night, try issuing a BACKUP LOG TRUNCATE_ONLY command during off hours when nobody should be using it.

Wadie,

I should have mentioned the real issue for you. Even in Simple mode, SQL Server writes to the transaction log. The only thing simple does is not allow a backup. This is why your log is written and continuously growing. If you decide to keep Simple mode, you need to create a script/job to execute a checkpoint which truncates the log.

Jer

ok, thanks all for your advice

It's also worth mentioning that if you have a single, large transaction running that even though you use the BACKUP LOG with NO_LOG command it will not allow any active transactions to be truncated. You typically run into this issue when you're doing a mass update on a large table such as F0911 or F4211.