December 3, 2004 at 6:57 am
Hello,
we have recently upgraded one of our production servers from SQL 7.0 to SQL 2000. I set up the primary database to use the simple recovery model. The maintenance plan does a full backup once each day. In addition, Differential Backups are performed each hour.
The problem is that the transaction log data file is growing at a rapid rate. It was my understanding that under the simple recovery model, the log would truncate on checkpoint (ie, after the backups occur). Does anyone know how to prevent the transaction log file from continuing to grow?
Thanks for your assistance!
December 3, 2004 at 7:37 am
the checkpoint should occur every minute or so. Are you sure of the recovery model? Also, dbcc opentran, there might be an open transaction which is causing it to grow.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 3, 2004 at 7:41 am
Hi Steve, yes the recovery model is set to Simple, and there are no open transactions. I am really stumped by this one...
December 3, 2004 at 8:58 am
The interval between the checkpoints is based on the number of records in the log and not time. The fact log keeps growing does not necessary triggers checkpoint. See BOL "Checkpoints and the Active Portion of the Log".
Unless you don't have enough disk space you should not concern too much about log size. However, you can always shrink the log during off-peak hour.
December 3, 2004 at 1:49 pm
Hi,
Is your application inserting/deleting data by recordsets? some transactions are implicit. There may be no open transaction when you were looking at the log, but for example, just before and just after. And simple model truncates log, not shrinks the file.
Use DBCC Shrinkfile to get the log file smaller.
Ask your programmers to evaluate the application if they can use smaller recordsets
Yelena
Regards,Yelena Varsha
December 3, 2004 at 2:28 pm
From a slightly different point of view...
If you are taking daily full backups, and hourly differentials, may I assume that your application is transactional? You may be better off setting the recovery model to full, and taking hourly (or even more frequent) transaction log backups, rather than the differentials. Your backups may end up being smaller, you'll have control over the size of the transaction log based on how frequent the backups are taken, and most importantly, you'll be able to recover to any point in time should the need arise.
On the other hand, you don't want to be shrinking the transaction log on a daily basis. It should find a size where it is optimal and stay there. If you shrink it, it will have to grow again in order to process the transactions. Anytime the transaction log grows, its growing so that it can process those transactions, and that hurts performance.
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply