Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

transaction log backup understanding sql

Posted on 2015-01-14
2
Medium Priority
?
97 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 52

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 23

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

636 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