Solved

SQL Server 7 Backup Transaction Log

Posted on 2004-09-30
2
180 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 250 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

710 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