Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Unanswered: Huge Transaction Logs

    Hi,

    I need help!!! The situation is as follows: on both our development servers, we have transaction logs that have grown exponentially - by at least a GB or two every day for the last week. We have no space and I am unable to truncate or shrink the log file. Last week the senior dba/developer deleted the transaction log backup job because of space issues on both servers. Since then, we have had the transaction logs grow. Is this a coincidence? The general concensus is yes. I understand that a full backup should release the empty space in a transaction log but for some reason, even though the backup job is not failing on one of our servers, the logs are still not freeing up space. Any suggestions? We are adding more drives to the servers today but that solution will only work if the logs don't keep growing. Please help.

    Maria

  2. #2
    Join Date
    Jul 2003
    Posts
    18
    I think i've had the same problem. I think you need to EXPLICITLY back up the tx log in order for it to shrink

    Funnily enough, I just logged in to the forum because my tx log is 14 GIG!!!

  3. #3
    Join Date
    Jul 2003
    Posts
    5
    The Full backup will NOT free up space in the Transaction Log. It will backup the complete database (data and index) and will leaves the logfile just as it is: growing
    Since it is a development environment I don't think the LOGfile is needed for auditing and I suggest:
    1. A Full backup
    2. BACKUP LOG WITH TRUNCATE_ONLY
    3. If still required a SHRINK

    HtH

  4. #4
    Join Date
    Jul 2003
    Posts
    24
    That's what I thought but I just wanted to ensure we were on the right track. Another question. even though it's the dev environment and we don't need point in time recovery, I can set up a tran. log job to run nightly to backup and truncate the tran. log right?

    Anyway, thanks again for the response.

    Maria

  5. #5
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    yes, you can create a backup job and schedule it to run at the time you want.
    Johan

  6. #6
    Join Date
    Jul 2003
    Posts
    24
    Hi,

    Yes, I know I can create a tran. backup job to run nightly - I just didn't want to have it run as frequently as it does on production hence only at night.

    Thanks for all your replies everyone. I greatly appreciate it.

    Maria

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Make sure BACKUP of Tlog will not reduce the physical size of Tlog file, and only log truncation occurs.

    To reduce the physical size you must use DBCC SHRINKFILE.

    Also enable Tlog backups on the database to ensure the maximum availability in case of any failure, it doesn't hurt any of the performance on the database.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Aug 2003
    Posts
    9
    When you don't need a recovery in time, you don't need the logfile.
    In that case set your database recoverymodel to Simple.
    Then your log file will not grow, and you don't have to backup and schrink it.

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    I have to agree with Lambik, if you don't need point in time recovery and there is no other overriding technical reason to have your database set to full recovery mode, then I would simply set the database options to 'Simple' recovery. Your log file will not automatically shrink, however. You should right-click on the database, select All Tasks/Shrink Database. When the Shrink Database dialogue pops up, select Shrink files. Select your log file from the drop-down list and then click OK. It should set your transaction log to about 1,024KB.

    After that you should be okay. The actual size may vary a bit from time to time (esp. if you import a large amount of data or perform some other intensive activity), but in general it should remain manageable.

    Be sure when you set up your back up plan that you do NOT select to backup the transaction logs for any data base that is set to Simple recovery. The jobs will fail every time.

    Regards,

    Hugh Scott

    Originally posted by Lambik
    When you don't need a recovery in time, you don't need the logfile.
    In that case set your database recoverymodel to Simple.
    Then your log file will not grow, and you don't have to backup and schrink it.

Posting Permissions

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