Need for SQL log files after backup

Posted on 2011-10-11
Last Modified: 2012-06-21
What is the need for SQL log files after backup has occurred?
My understanding is that the log files are only required for a restore, but after a SQL Full backup what are they needed for?
In addition to this i also thought that after a SQL DB Full backup the log files were compressed / emptied?
Question by:Singnetsvc
    LVL 29

    Expert Comment

    by:Rich Weissler
    You have log files, and backups of the log files.  

    All actions on the database are written first to the Transaction Log, and once committed they are written to the database.

    Under the Full Recovery Mode (after you've at least performed the first full backup), space in the transaction log isn't overwritten until a transaction log backup is performed.  Once backed up, chunks of the transaction log are made available to be overwritten if those sections of the transaction log contain no open transactions.  The transaction log doesn't shrink though, after a backup... sections are just allowed to be overwritten.  Simple Recovery mode is similar, except a transaction log backup isn't required for a section of the transaction log to be available to be overwritten.  (But... it's important to remember that open transactions can prevent space in the Transaction Log from being re-used.)

    In Full Recovery mode, the transaction log backups (along with Full backups, and differential backups if you are using those) are used (i.e. make it possible) to restore the database to a specific point in time.

    But in general, the SQL transaction log files are used for every transaction... they are written to the log file first.  (Even the minimally logged transactions are written to the log files in some form... the minimally logged transactions may just take shortcuts to accomplish things... like un-allocating database pages rather than specifying specific deletes for example.)
    LVL 29

    Accepted Solution

    I know you probably didn't ask the question to get a link to a series of articles, but I've become quite enamored of the SQLServerCentral Stairways series, and their series of Transaction Logs are quite good.  If my answer is insufficient, or confusing I suspect the first and third steps would probably answer all your questions.
    LVL 3

    Author Closing Comment

    Great answer,  very simple and easy to following,  great link ref!!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    794 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

    15 Experts available now in Live!

    Get 1:1 Help Now