that's actually a sql server issue, not dotnetnuke. With SQL/MSDE the transaction log grows until the data is in a fixed state (known as a checkpoint), at which time the transaction log is truncated as the data integrity is guaranteed. How often these checkpoints happen depends on the level of logging you have set up, which is known as the recovery model. You can read about recovery models here

If you choose to stick with the default of full, then the transaction log will only truncate when you do a full database backup. You can do this either by a script, or a sql job, but the easiest way is to use maintenance plans . Often setting your database to backup nightly is enough to keep the logs down to a reasonable size, but you may want to do more regular backups depending on your site. As part of the maintenance wizard, you can also run some optimising tasks.

If this issue is happening every week or so, I would suggest that you're not backing up your database, so the transaction log never get's truncated. I'd recommend that you do at least nightly backups.

Note: In many sites I have, that don't change content too often, and aren't regarded as business critical, I change the recovery model to simple.

Cathal