SQL Server 7 Backup Transaction Log

Ok,

I have quite a few Transaction logs which are very large 5-6 Gig. I have just setup my transaction log to backup under the general maintence plan we have setup. Is that going to reduce the file size of those logs, or is there something else I need to do to get these things down to a manageable level.

LVL 2
DarksbaneAsked:
Who is Participating?
 
mikkelpCommented:
Your transctionlog will be automatically shrunk, when you backup your database...  Maybe your maintenance plan fails because "simple recovery model" doesn't allow backup of transaction log, thus the backup-job fails.

You may want to consider which level of backup you need and set your maintenance plan accordingly:
1. Must be able to restore data up until the second the database crashed
2. Must be able to restore data up until the latest backup (ie. the night before)

ad 1) Set Database Recovery Model  to "Full". Every transaction is written to the transaction log and preserved here. In case your database breaks, you can restore the data and "apply" the transaction-log to the latest backup, thus giving you the exact data of the database at the time of the crash. When you backup the database, the stored transactions are no longer needed for a recovery, and the Transactionlog is truncated. In this model you have the option to backup the transaction Log as well.

ad 2) Set database recovery Model to "Simple". Every Transaction is written to the transaction log during the action and then "truncated". A recovery of the database involves replacing the crashed data with the latest backup, which may be a few hours old.  On a backup of the database, the transactionlog is wiped (almost totally). In this option, you cannot backup the transaction log (The job just fails). I don't know if the log will be wiped if the DBbackup + TLBackup fails, but I'm thinking this may be your problem

Mikkelp
0
 
geotigerCommented:
You need to trancate your logs.

This will truncate the transaction log for you:

DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.