sql log backups and space saving

Posted on 2014-08-15
Last Modified: 2014-08-21
can anyone tell me in layman's terms how backup up transaction logs saves space? Once backed up are they deleted and a fresh one created? Also is there a default value for the transaction logs maxsize? What is this value in GB? This is SQL 2005.
Question by:pma111
    LVL 44

    Accepted Solution

    Hi pma,

    First thing to know is that backup transaction log are only available for databases with full or bulk_logged recovery model.

    Second thing is that backing up a transactiong won't create a new file but will empty the actual transaction so the file will have more space to receive new data. That's how allows saving space because it going to reuse the now emptied space to write data on it. If you won't backup the transaction log the file will grow until the limit of the disk (if autogrowth is enabled).

    This takes us to the next question, that is the default value for maximum size is 2TB but you can reconfigure it using the next command (in the example I set it to 2GB):


    Open in new window

    Hope that I could answer your questions.
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    Good explanation given above
    Important detail for clarity: the space will only be marked reusable the physical file will not actually shrink.
    Often newbie dba implement a regular shrink of the log files which results in waste of io

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    754 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

    21 Experts available now in Live!

    Get 1:1 Help Now