Solved

transaction log backup understanding sql

Posted on 2015-01-14
2
74 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
2 Comments
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 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 250 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 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

9 Experts available now in Live!

Get 1:1 Help Now