SQL Server 2005 Transaction Log too big

Question

I am running SQL Server 2005.

My db backup scheme is:
Recovery model: FULL
Backup Type: Full
Backup component: Database
Backup set will expire: after 0 days
Overwrite media: Back up to the existing media set, Append to the existing backup set

The db is writing to 250GB drive (232GB actual).

My _Data.mdf file is over 55GB and my _Log.ldf is over 148GB.

We ran into a situation where our drive was filled today. I moved our ab_Full.bak and ab_Log.bak files to another drive to make space - about 45GB. Five hours later, free space is at 37GB.

I'm new to managing SQL server; so, I have some basic questions about my backups.

I know I need to update the db to start managing the transaction log size to help prevent this problem in the future. So, assuming I have enough free space, I:
1. right click the db and choose Backup
2. set 'Backup Type' to 'Transaction Log'
3. change 'Backup set will expire' after to 30 days
4. click 'ok'

My understanding is this will move 'closed' transactions from the transaction log to a backup and truncate the transaction log.

Is this plan sound? Will I need to manually resize the log file afterwards?

Thanks for your time.

Answer 1

Are you backing up the transaction log at any time at all? If you are using the FULL recovery model, then you need to back up the transaction log in addition to backing up the main database, or if you don't want to back up the log (why would you then use the FULL recovery model?) then at least truncate the log at some regular interval.

You should back up the transaction log before every full backup (and keep it as long as you keep the previous full backup) so you can restore to any point in time since the first full backup you've kept. Also, it might be worth backing up the transaction log more often (the total size is the same) in case something bad happens between two full backups.