• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

transaction log backup understanding sql

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.
2 Solutions
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
Steve WalesSenior Database AdministratorCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now