Results 1 to 5 of 5

Thread: huge log files

  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Thumbs up Unanswered: huge log files

    Hi..

    My Dev database has a very big log file..even after truncating the log file I am not happy with the size.

    Found the following way to create a new log file:
    Detach database..delete log file attach database using sp_attach_single_file_db which then recreates the log file (504kb))

    Very happy.

    My question is: Has anybody else used it this way and if so..was there any problems afterwards. I just want to set my mind at ease that this is an acceptable way of 'wiping' the log file.

    Regards

    Bert

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    This is an easy way of reducing the log file size.
    You should always take a backup first in case the attach fails.

    Shrinking the log file should work though - do you have the database in simple recovery mode?

  3. #3
    Join Date
    Nov 2003
    Posts
    3

    Huge Log Files

    Well..the recovery model is really irrelevant from a dev perspective. I suppose a simple recovery model would make it smaller but even then you could sometimes do something on your dev database that changes a whole lot of stuff and posiibly not recover from if you are using a simple recovery model.(I am not too clued up around the recovery models..just know that once logs more that the other.)

    But we had this dev database that had a 580MB log file and the actual data file is approx 60MB)..tried to change to a simple recovery model but the log file was still approx 180MB in size...Thus I opted for the attaching process. Yup allways make full backup before attempting these issues..

    Thanx for the reply though.

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    The tr log is a circular file - free entries are reused when the file wraps round.
    If the recovery model is not simple then entries will not be released for reuse until the log is backed up and the file will just keep growing.
    Changing the recovery model will release completed entries for reuse but not shrink the file - dbcc shrinkfile will do this.

    >> you could sometimes do something on your dev database that changes a whole lot of stuff and posiibly not recover from if you are using a simple recovery model.
    Unless you have a recovery strategy implemented the recovery model won't make any difference to this - see point in time restore.

    Apart from bulk operations the various models log the same amount - the difference is in when the log entries are reused.

    See shrinking the transaction log in bol.
    You will need to get the active entries to the start of the tr log to release the maximum space by a shrink.

  5. #5
    Join Date
    Nov 2003
    Posts
    3

    Huge Log Files

    Interesting...will do some homework on this..thankx for the info

Posting Permissions

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