?
Solved

transaction log backup understanding sql

Posted on 2015-01-14
2
Medium Priority
?
91 Views
Last Modified: 2015-01-27
When you run a maintenance task in studio to backup trans logs.... does this process shrink or offload the ldf file associated with the chosen databases?  How does this work?  I am having trouble with log file management but I do need the full recovery model in place for the database in question.
0
Comment
Question by:jamesmetcalf74
[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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 40548961
Transaction log backups truncates the log but do not shrink the file, i.e. it empties the file but do not reduce the file size so the engine knows that it can reuse the space.
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 1000 total points
ID: 40548976
The transaction log file is a different animal in SQL Server than Oracle, for example.  In Oracle as the online redo logs fill, they are written out to separate archived redo logs, which you then backup.

In SQL Server, the Transaction Log is a single file.  As updates occur, redo information is continually written to the transaction log and if no action is taken by the DBA, the Transaction Log will grow forever.

When a transaction log backup is taken, the space that is used in the transaction log becomes available for reuse.

There's some notes in the documentation about managing the size of your transaction log.

Regularly shrinking your transaction log is probably not best practice.  Take regular log backups and size your transaction log so that it can handle the workload in between backups.  So, for example, if your Transaction log backups run hourly, make sure that you pre-grow your transaction log to a size that can handle the volume of transactions in that window.

Auto growing your transaction log as it needs space is an expensive operation and should be avoided.

I'm not sure, from your question, if you have a transaction log that needs shrinking or not.  If you do, have a read of this article which also links to several other pieces of the documentation and blog posts from some noted SQL Server experts on proper Transaction Log management for SQL Server.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 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