Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 7 Backup Transaction Log

Posted on 2004-09-30
2
Medium Priority
?
182 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

660 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