SQL Server 2005 transaction log is too large

Question

I am running SQL Server 2005.

My db backup scheme:
Recovery Model: FULL
Backup Type: Full
Backup Component: Database
Backup dialing expires in 0 days
Overwrite media: back up to an existing set of media, add to the existing set of backups

The DB writes a 250 GB disc (actual value of 232 GB).

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

We were faced with a situation when our disk was full today. I moved our ab_Full.bak and ab_Log.bak files to another drive to make the space about 45 GB. Five hours later, the free space is 37 GB.

I am new to managing SQL Server; so I have some basic questions about my backups.

I know that I need to update db to start managing the size of the transaction log in order to prevent this problem in the future. So, if I have enough free space, I:
1. right-click on db and select "Backup" 2. set "Backup Type" to "Transaction Log"
3. Change "Reserve Set Expires" after 30 days
4. Click OK

I understand that this will move “closed” transactions from the transaction log to the backup and truncate the transaction log.

Does this plan sound? Do I need to manually resize the log file?

Thank you for your time.

Answer 1

Do you back up your transaction log at any time? If you are using the FULL recovery model, you need to back up the transaction log in addition to backing up the main database or if you do not want to back up the log (why should you use the FULL recovery model?), At least trim the log on some regular interval.

You must back up the transaction log before each full backup (and keep it until you save the previous full backup) so that you can restore at any point in time from the first full backup. In addition, you may need to back up the transaction log more often (the total size is the same) if something bad happens between the two full backups.

Answer 2

The best procedure is to back up your log file regularly. At the same time, for “catastrophic” scenarios like the one you described, you can use this snippet to reduce the size of your log: http://www.snip2code.com/Snippet/12913/How-to-correctly-Shrink -Log-file-for-sql