Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Unanswered: Msg 9002, Level 17, State 4 The transaction log for database 'xxx' is full due active

    Msg 9002, Level 17, State 4 The transaction log for database 'xxx' is full due 'active_transaction'

    Hello All,

    It's been some time since I've been on this side of the fence. I seem to get this error about the transaction log being full. This is a cleanup effort. There was additional error messages:
    (0 row(s) affected)
    Checking identity information: current identity value '0' or 'NULL'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.'

    To me this mean while running this previously created script all transaction are logged without any reset to the database but is causing the logged to fill up with garbage transaction checks. This process has appear to have no impact on the database and will trickle down to no impact on the application during the testing process. Please advise.

    Thanks
    Garry D
    Back on this side of the database fence.
    Oracle/SQL Server/Greenplum

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    This could be caused by a maintenance plan trying to rebuild/reorganize indexes. Any chance of giving the transaction log more space to use? Also, if this database is in FULL recovery mode, you might want to check on the transaction log backups to make sure those are running cleanly.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Note the current size of the log file. Make sure you have a full backup, then perform trx log backup. Then, set the database to SIMPLE recovery mode to reset the virtual log size. If necessary - even shrink the log file. After that - allocate the log file size to 1.5 to 2 times the size of the log before you started. Then schedule full backups followed by trx log backups at least every 2 hours. To ensure you don't find yourself in this pickle again, - you can even set the db to SIMPLE recovery mode before you start your index/statistics maintenance tasks. Of course after that, - set back to FULL, then full backup, letting scheduled trx log backups to pick up as usual.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jan 2016
    Posts
    24
    Provided Answers: 1
    Another option to get rid of this error is to disable the job which is filling the log until the job properly investigated then use log_reuse_wait_desc command. See here for other possible solutions: http://www.sqlserverlogexplorer.com/...nsaction-full/

Posting Permissions

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