Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014
    Posts
    5

    Unanswered: Transaction log growing uncontrolled.

    This is my problem. The transaction log of a database grows until it runs out of disk space. If disk space is full, all databases on instance may get problems. Because of this i have set a limitation on how much it may grow, up to 40 GB. It grows in steps of 100 MB. It reaches its limit a couple of times a week, causing the application to hang.

    The database file itself is about 2,3 GB large.

    The SQL version is 10.50.4276, SQL2008R2 SP2.

    The recovery model is FULL.

    I have a backupjob that runs a FULL Backup at midnight.
    And a backupjob that runs a LOG backup every 30 minutes.

    Both finishes with success.
    However, the transaction log is never truncated, the unused space is never released.

    I have checked for "long running jobs", it sometimes sys "backup_log", sometimes "active_transaction".

    Could a workaround be to set the recovery mode to simple, and create a full-backup job that runs every 30 minutes for this database? It is a critical database....

    Hope someone has any good ideas, iīm all out of them....

    //Jens

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Consider changing your full backup script to include a step to truncate the log after a successful full backup

    Pre SQL Server 2008
    Code:
    BACKUP LOG databasename
      WITH TRUNCATE_ONLY;
    
    DBCC SHRINKFILE (databasename_Log, 1);
    SQL 2008 onwards
    Code:
    ALTER DATABASE databasename
      SET RECOVERY SIMPLE;
    
     DBCC SHRINKFILE (databasename_Log, 1);
    
    ALTER DATABASE databasename
      SET RECOVERY FULL; -- Or whatever you had before we set it to SIMPLE ;)
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Does your application require point-in-time recovery? Or would Simple do?

    Back in my DBA days I know there were ways to set off alerts for disk space.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Feb 2013
    Location
    perth australia
    Posts
    24
    sounds like you have a single long running transaction that doesn't commit. You will have to investigate whether this is the case. maybe use dbcc opentran. Changing the recovery model might not help

  5. #5
    Join Date
    Jan 2014
    Posts
    5
    Thanks, this is what i do manually now, good idea to make a separate script for this databases backup. I will try this.

    Quote Originally Posted by gvee View Post
    Consider changing your full backup script to include a step to truncate the log after a successful full backup

    Pre SQL Server 2008
    Code:
    BACKUP LOG databasename
      WITH TRUNCATE_ONLY;
    
    DBCC SHRINKFILE (databasename_Log, 1);
    SQL 2008 onwards
    Code:
    ALTER DATABASE databasename
      SET RECOVERY SIMPLE;
    
     DBCC SHRINKFILE (databasename_Log, 1);
    
    ALTER DATABASE databasename
      SET RECOVERY FULL; -- Or whatever you had before we set it to SIMPLE ;)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •