We noticed recently that the transaction log for one of our databases had grown over time to 4gb (Database itself is 1gb).

We have solved this for now by running DBCC SHRINKFILE

We would like to have our maintenance plan do this, but cannot see an option anywhere.

Can someone please advise if this is possible?

Also - Just to check the logic here, we will backup the database, then truncate the log, then the next business day starts, so at any time we can, if required restore the previous nights DB backup then use the transaction log to restore to a point in time???

If you backup the database and then truncate the log (which you can't do anyway in SQL 2008), you won't be able to take any log backups and your only recovery option in the case of a problem would be the previous full backup. Definitely not a good plan.

Shrinking the log regularly is also not a good idea. If it really needs to be that size it will just grow back to that size and when the log grows, everything slows down.

No, but it makes the space used by the part that has been backed up available for re-use - so basically, if you back it up regularly, it will get to an equilibrium size.

If you haven't been backing it up you will need to back it up and then do a one-time shrink to get it back to a more sensible size. Once it reaches equilibrium it should then stay at that size - if it grows suddenly investigate (might be someone did a massive one-time DELETE ...). You may find that housekeeping routines (REINDEX etc.) put more pressure on the size of the TLog than any of your normal day-to-day transactions - if that causes unacceptable size of TLog you'll have to set up a housekeeping strategy to work around it.

The loss of your server would mean the loss of your TLog, so if you can copy the Tlog backups to another location soon after they are created that is prudent.

However, TLog backup is often useful in other disasters:

How did all the records get deleted?

Database is corrupted (Make a final TLog backup, restore from a FULL backup before corruption occurred, then all Tlog backups since - usually possible to recover the database with no data loss).

Need to move to another machine with minimum downtime:

Take full backup, restore on new machine (with NORECOVERY)Take old server offlineMake TLog backup (tiny, small and quick)Restore TLog backup on new server (quick operation) using RECOVERY to make the new server database available

Backup the TLog every 15 minutes, and then your maximum data loss is 15 minutes.