Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Server 7 Backup Transaction Log

Posted on 2004-09-30
2
Medium Priority
?
188 Views
Last Modified: 2008-02-01
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.

0
Comment
Question by:Darksbane
2 Comments
 
LVL 12

Expert Comment

by:geotiger
ID: 12194027
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
 
LVL 4

Accepted Solution

by:
mikkelp earned 1000 total points
ID: 12197924
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question