Post reply

Best way to shrink LARGE log files

  • May 10, 2004 at 11:40 am

    #85509

    HI. I noticed that I have about 4 LARGE log files When I did a dbcc sqlperf(logspace) I spotted them imediately. I've never modified the sizes of my log files. I have 3 questions:

    1) Best method to shrink the log files

    2) Best method to determine what size the files should be. I can see the % log space used and was wondering if i calculate what size that is by figuring the % space used against the actual size of the file.

    3) one of the large log files is with the MSDB database. does this log file need to be handles differently?

    THANKS SO MUCH !

    Juanita

  • May 10, 2004 at 11:17 pm

    #505900

    You might need to consider if your log file /recovery plan is correct. do you need it on full, you might find it easier to have it on bulked log or simple.

     

    Try BOL truncating logs, or if you want to shrink them try

    DBCC Shrinkfile this is also in Books on line.

     

    hope this helps

  • May 11, 2004 at 5:19 am

    #505944

    When i have to clear a log i use:

     BACKUP LOG DBNAME WITH TRUNCATE_ONLY

     DBCC SHRINKFILE ('LOGNAME', 1)

  • May 11, 2004 at 8:17 am

    #505979

    Thanks a bunch. works like a charm.. I wasn't doing the truncate only and this was holding me up.

    Juanita

     

  • May 11, 2004 at 12:36 pm

    #506029

    Now that I've got my log files down in size by doing the backup log <dbname> truncate only and then the shrink file command, should i THEN perform a full db backup with a tlog log backup? I  read somewhere in BOL about doing a db backup after a backup log.

    Does this sound familiar? 

    Juanita

     

  • May 11, 2004 at 10:05 pm

    #506073

    Absolutely. I had a similar problem once.

    BOL - refer "truncate_only"

    Although the transaction log may be truncated manually, it is strongly recommended that you do not do this, as it breaks the log backup chain. Until a full database backup is created, the database is not protected from media failure. Use manual log truncation only in very special circumstances, and create a full database backup as soon as practical.

    DB


    The systems fine with no users loggged in. Can we keep it that way ?br>

  • May 12, 2004 at 3:52 pm

    #506213

    A transaction log backup will perform a truncate after backing up the log.  Therefore, to keep your logs small, and have maximum protection, you should schedule regular transaction log backups between your full database backups.  If you do this, you should never have to do a backup log with truncate_only.

    Steve

  • May 12, 2004 at 3:55 pm

    #506214

    Thanks so much to everybody that replied. I think I got it now.  Sure helps to get first hand help in addition to BOL.

    A happy camper,

    Juanita

     

  • May 12, 2004 at 10:34 pm

    #506229

    Juanita, U can create a Database Maintenance Plan (which solves many problems for one shut); and watch your server with "SQL Check" (free from IDERA).

    All the best,

    Jimmy

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply