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

Need for SQL log files after backup

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?
  • 2
1 Solution
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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.)
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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.
SingnetsvcAuthor Commented:
Great answer,  very simple and easy to following,  great link ref!!


Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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