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.
jamesmetcalf74Asked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior 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.
0
 
Vitor MontalvãoConnect With a Mentor MSSQL 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.
0
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.

All Courses

From novice to tech pro — start learning today.