locked
database log full RRS feed

  • Question

  • what are the  option to free up the log?

    1. kill spid filling up log.

    2. backup the tran log - to disk or truncate only or no_log.

    3. change the database to simple recovery mode, delete ldf file and then revert back to full recovery mode.

    4. detach database, delete log file and reattach mdf again.

    I am especially interested to know if the last two are commonly used in case dba is not able to free up log using other ways.

    thanks.

    Tuesday, September 16, 2014 5:45 PM

Answers

  • First find why the log file is not truncating,

    SELECT name,
    	   log_reuse_wait
    FROM sys.databases;

    The recovery model dictates log behavior and whether the log will truncate on checkpoint or requires a transaction log backup, full and bulk logged recovery requires that you backup the log to keep the log size manageable, refer here to the different kinds of backups and their affect on the T-Log.

    The action you take will be dictated by the reason for the log not truncating.  There are numerous reasons that the log does not truncate including replication, mirroring, transaction log backup, active transaction, etc.  BACKUP LOG TRUNCATE ONLY is no longer supported as it removes the ability to restore the database to a point in time.

    The last two options are last ditch efforts to regain control of the log file size and should only be done after a full backup of the database is taken to insure that a viable backup is available.


    David Dye My Blog


    Tuesday, September 16, 2014 5:52 PM
  • the tlog just gets reused. You really never have to delete an ldf file unless in a very very rare situation.

    The first thing to understand when log file is full is to understand what is causing the log file not to be reused. David has already given you the query.

    Read the below link which gives full information of different factors that effect log reuse. Only if log backups are preventing your log file to be reused then taking a log backup will be helpfull.

    http://technet.microsoft.com/en-us/library/ms345414(v=sql.105).aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, September 17, 2014 8:06 AM

All replies

  • First find why the log file is not truncating,

    SELECT name,
    	   log_reuse_wait
    FROM sys.databases;

    The recovery model dictates log behavior and whether the log will truncate on checkpoint or requires a transaction log backup, full and bulk logged recovery requires that you backup the log to keep the log size manageable, refer here to the different kinds of backups and their affect on the T-Log.

    The action you take will be dictated by the reason for the log not truncating.  There are numerous reasons that the log does not truncate including replication, mirroring, transaction log backup, active transaction, etc.  BACKUP LOG TRUNCATE ONLY is no longer supported as it removes the ability to restore the database to a point in time.

    The last two options are last ditch efforts to regain control of the log file size and should only be done after a full backup of the database is taken to insure that a viable backup is available.


    David Dye My Blog


    Tuesday, September 16, 2014 5:52 PM
  • 1. kill spid filling up log.

    Not going to help. The spid will not be killed but be interrupted when the log can not be written to.

    2. backup the tran log - to disk or truncate only or no_log.

    Yes, this is the way to. That is, to disk. The TRUNCATE_ONLY and the NO_LOG options have been discontinued. You can switch to simple recovery and revert back, but then you will break your log chain.

    3. change the database to simple recovery mode, delete ldf file and then revert back to full recovery mode.

    4. detach database, delete log file and reattach mdf again.

    I am especially interested to know if the last two are commonly used in case dba is not able to free up log using other ways.

    Never delete your LDF files. You may not be able to use the database again.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, September 16, 2014 10:09 PM
  • switching to simple recovery mode and then switching back to full recovery mode, doesn't require deletion of old ldf file?
    Tuesday, September 16, 2014 11:54 PM
  • switching to simple recovery mode and then switching back to full recovery mode, doesn't require deletion of old ldf file?

    Affirmative. Never delete ldf files.

    BOL: Troubleshoot a Full Transaction Log (SQL Server Error 9002)



    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Edited by Kalman Toth Wednesday, September 17, 2014 12:09 AM
    Wednesday, September 17, 2014 12:06 AM
  • so what happens to the old ldf file? is it re-initialized and reused - after switching back to full recovery mode from simple recovery mode?

    Wednesday, September 17, 2014 2:42 AM
  • the tlog just gets reused. You really never have to delete an ldf file unless in a very very rare situation.

    The first thing to understand when log file is full is to understand what is causing the log file not to be reused. David has already given you the query.

    Read the below link which gives full information of different factors that effect log reuse. Only if log backups are preventing your log file to be reused then taking a log backup will be helpfull.

    http://technet.microsoft.com/en-us/library/ms345414(v=sql.105).aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Wednesday, September 17, 2014 8:06 AM
  • It seems from your question that you have a very poor understanding of the transaction log file.

    The log file is written to in a cyclic manner. It only grows if you fail to back it up often enough, or it grows because of an on-going transaction. (Or other reasons like hangups in replication.)

    Are you backing up the transaction log reguarly?

    And never delete a transaction log file.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, September 17, 2014 8:36 AM