Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: transaction log full

    Today, one of our backups failed due to the transaction log being full. I expanded the transaction log and then the backup completed normally. For a short term resolution, I increased the maximum allowed size on our transaction log files.

    We are doing a database backup only as follows:
    backup database xxxxx to xxxx_data with init

    I did find a note stating that the transaction log will eventually become full if doing a transaction log backup only. We do not generate a lot of transactions so I had not seen a need to backup the log during the day. The full database backup is done daily.

    My question is, in order to permanently resolve this error do I have to:
    - take a transaction log backup as well. Should I take it take right after the full database backup or at some later point.
    - do I have to schedule/run the shrink database command.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I would respectfully disagree with the note that you found about transaction log eventually getting full if doing trx log dumps only, simply because of the nature of the log architecture and what a backup does to it (either full or log dump only.) The ONLY reason the log eventually is getting full is due to uncommitted transactions, which CANNOT be cured by ANY backup!

    As per frequency and sequence of trx log dumps, of course you need to do it before the full backup, providing your db is set for Full or Bulk-Logged Recovery mode.

  3. #3
    Join Date
    Jan 2004
    Posts
    5
    We had not been doing log backups just database backups

  4. #4
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    put the database to Simple recovery mode. This will take care of the problem.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I suspect the database is already in Simple Recovery mode. And if there is a lot of data being updated/inserted/deleted in large bulks, - this will not take care of the problem, because when checkpoint occurs, not all of the transactions are committed, which results in growth of the log file (see my previous post.)

  6. #6
    Join Date
    Jan 2004
    Posts
    5
    The database was in full recovery mode. Have now implemented daily transaction log backups.

Posting Permissions

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