Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    31

    Unanswered: How come I'm getting error saying transaction log Full?

    Hello,

    I have a process that failed with the following error message. The SQL server error messages also said the Transaction log was full and there was not enough disc space.

    Strangely, when I checked all the drives on the server, there was plenty of free space, the smallest amount free on one drive, where SQL server is located was 20GB. I am confused.

    I backed up the DB to another server and deleted a lot of the transaction logs and now the drive has 30GB and is okay.

    Does anyone know how the transaction log fills up? Can I change a setting somewhere to increase the maximum size of the transaction log? Or maybe change the location where it is saved to another drive? Or have it automatically cleared out every once in a while?

    Any of your input is greatly appreciated.

    Thanks.

    Rodney

    --------------------------------------------------------------------------
    DS-DBMS-E400: UDA driver reported the following on connection 'Data Target
    (ODBC)':
    DMS-E-DBPARSER, The underlying database detected an error during processing of
    the SQL request.
    [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database
    'ancosalesdm' is full. Back up the transaction log for the database to free up
    some log space.
    (for details, see Build_SAL_FA_ShipSKU_0584.log)
    [PROGRESS - 00:32:42] Build Node 68 'SAL_FA_ShipSKU'; failed
    DS-DBMS-E400: UDA driver reported the following on connection 'ALIAS_00DF1E74':
    DMS-E-GENERAL, A general exception has occurred during operation 'execute
    immediate'.
    The log file for database 'ancosalesdm' is full. Back up the transaction log
    for the database to free up some log space.
    DMS-E-GENERAL, A general exception has occurred during operation 'execute
    immediate'.
    General SQL Server error: Check messages from the SQL Server.
    DS-DBMS-E400: UDA driver reported the following on connection 'ALIAS_00DF1E74':
    DMS-E-GENERAL, A general exception has occurred during operation 'rollback
    transaction'.
    The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
    DMS-E-GENERAL, A general exception has occurred during operation 'rollback
    transaction'.
    General SQL Server error: Check messages from the SQL Server.
    --------------------------------------------------------------------------

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    In Enterprise Manager, right click the DB and go to Properties. From there you can check to see if your Transaction Log has a size limit, and can adjust it if necessary.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If you are not doing transaction log backups, you need to set the recovery mode on this database to Simple. This will make sure it doesn't just keep growing until it fills up.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    all this is good, but if a transaction affects a really large amount of data the recovery mode is irrelevant. the log will continue growing (if the setting to allow growth is on) until it reaches the limit specified or the limit of the disk.

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    That's true, and if this is the case, you will need to break the transaction into smaller pieces. You can also insert CHECKPOINT at various places in your transaction if you need to, but this would be a pretty rare case.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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