• subscribe
August 21, 2002 12:00 AM

Log Backups Paused for Good Reason

SQL Server Pro
InstantDoc ID #26032

Log backups are an important part of a full database backup. SQL Server backs up the transaction log as part of a full backup to make the full backup transactionally consistent when the full backup is restored. Before you can back it up, the transaction log needs to be accessible and complete: SQL Server must have access to all log activity that has occurred throughout the full backup, and the log can't have been cleared. However, the default transaction log backup behavior is to back up and then clear what was backed up. If log backups were allowed during a full backup, the full backup couldn't back up the log and thus would be transactionally consistent during a restore.

For example, let's say you start a full backup at 2 A.M. and it completes at 4:47 A.M. In SQL Server releases before 7.0, the restored image reflects the database at 2 A.M., whereas in the later releases, the restored image reflects the committed transactions as of 4:47. The only way to get all the information is to ensure that the activity that occurred during this time is backed up and stored with the full backup.

During a restore, the log information is replayed to make the full database backup transactionally consistent with the state of the database as it was at the time the backup completed. However, although the full backup includes a portion of the log, it doesn't clear the transaction log. Transaction log backups are the only action that should ever clear the transaction log. As of SQL Server 7.0, you have no reason to manually clear the transaction log in a properly maintained database. You can include the two statements

BACKUP LOG dbname WITH NO_LOG 

and

BACKUP LOG dbname WITH TRUNCATE_ONLY 
if you want to manually clear the log, but you don't need to. If the transaction log becomes full, a regular transaction log backup will clear whatever was backed up. Regular, consistent transaction log backups will help keep the transaction log small and give you the lowest risk of lost data.


ARTICLE TOOLS

Comments
  • Anonymous User
    8 years ago
    Nov 16, 2004

    How would this work for Yukon? Please let me know at arkhas@gmail.com

  • Amuraim Rotem
    9 years ago
    Jul 21, 2003

    I can't figure how to correct the situation

    Microsoft OLE DB Provider for SQL Server error '80040e14'

    The log file for database 'atrafdating' is full. Back up the transaction log for the database to free up some log space.

    /left_frame_iframe.asp, line 42

    Microsoft OLE DB Provider for SQL Server error '80040e14'

    The log file for database 'atrafdating' is full. Back up the transaction log for the database to free up some log space.

    /members_main.asp, line 97


    Please answer to amuraim@yahoo.com

  • Vinícius Oda
    10 years ago
    Nov 19, 2002

    "(...)thus *WOULD* be transactionally consistent during a restore" (1st paragrph)

    Isn't it *WOULDN'T* ?

You must log on before posting a comment.

Are you a new visitor? Register Here
  • SP1?
    I know there is a SP1 for SQL 2008 R2 available....and there is a "feature pack" as well... ...
  • SQL database mirroring
    I have SQL Server 2008 R2 Enterprise 64bit on Windows 2008 R2 Enterprise 64bit.  Each SQL Server has...
  • Dell Compellent Disk Drive
    Does anybody has experience with Dell Compellent Disk Drive? Basically, this system manages all disk...
  • Sql server performance tuning
    I need to find a tool that help me to optimize sql server,queries,improve the performance and solve ...