Solved

SQL Server 7 Backup Transaction Log

Posted on 2004-09-30
2
177 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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now