Transaction Log Backup - SQL Server 2000

[jetski18]

Hello,

I'm new to this and need some help. I have 8 DTS packages that run every night. They truncate the previous nights data, for separate tables and reload the DB. None are running right now and the Error is "The log file for database FACSQL is full. Back up the transaction log for the database to free up some log space." I know this happens often enough that I have to fix it. I couldn't get my answers from online sites, so I'm hoping someone can help me. I think I need the following:
Code to Backup
Code to the Shrink Logs
Code to Change Virtual Log file size
I'm just guessing on these. Also, once I do all this, should I create a DTS package that performs this regularly so I don't run into the issu in the future?
Thank you!

 

[SQLSister]

First are all the tables being refreshed? If so change the database to simple recovery mode and the transaction log will grow much less quickly. Transaction logs grow until they are backup up, so yes you need to schedule backups of the transaction log not just the database. You can schedule these as a job instead of a DTS package.

Look up backup in Books online for the backup syntax, you want the backup log part. It truncates automatically when doing that but to recover the space you will need to shrink the database log afterwards. If you must truncate manually (you have run out of space) the backup command can just do a truncate as well. Do not do this without immediately running a full database backup afterwards as it breaks the backup chain and recovery is a problem.

"NOTHING is more important in a database than integrity." ESquared