SQL Server Performance

Log file is full

Discussion in 'General DBA Questions' started by pcsql, Jun 7, 2004.

  1. pcsql New Member

    I'm getting the Msg 9002:

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

    The log file size has grown to 22G and there is only about 2G free space left on the hard drive.

    I guess I should issue a Backup log with no_log.






  2. Luis Martin Moderator

    You can shrink log file.
    What recovery model do you have?, because if it is full, then you have to backup transaction log frecuently and truncate log to reduce log size.
    If you don't backup transaction log as part of backup plan frecuently (I mean: full backup once a day) then you don't need full recovery model.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. pcsql New Member

    The recovery model is full.
  4. derrickleggett New Member

    If you aren't doing transaction log backups then you need to set it to simple. This should fix your issue.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. satya Moderator

    Otherwise use DBCC SHRINKFILE to shrink the Transaction log size, and decide whether to keep Transaction log backups, if so schedule in regular intervals or follow as Derrick referred.
    But ensure the full backups are maintained if SIMPLE recovery model is followed.

    On above BOL refers error 9002:

    The user action that is appropriate to you depends on your situation. Potentially, possible actions include:
    Backing up the transaction log
    Freeing disk space
    Moving the log file to a disk drive with sufficient space
    Adding or enlarging a log file


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page