Post reply

transactional log remedy

  • September 6, 2010 at 10:02 am

    #387735

    HI all,

    I have some doubts and misconceptions, please correct me.

    Senario :

    i have database running in Full Recovery Model

    i take full backup on daily basis and transactional log backup every two hours

    now please tell me when we take Transactional log backup , DOES IT TRUNCATE LOG automatically ???

    or do i have to Truncate transactional log by myself ???

    using dbcc shrinkfile ('db_logs',100) with no_infomsgs

    please clear my concept...

    Thanks

  • September 6, 2010 at 10:07 am

    #1216979

    No, it does not reduce the size of the file, just makes the space available to be written to again. But unless it was an unusual operation that caused it to grow a lot more than the normal amount in a 2 hour period, why would you want to shrink it?

    You still need to have the space available for the next time it grows, but now it has to physically grow the file again, costing more I/O and potentially fragmenting across the disk.

  • September 6, 2010 at 10:11 am

    #1216982

    Truncate and shrink are two different concepts.

    Truncating the log makes space within available for reuse. Log backups do this.

    Shrink releases unused space to the OS. Backup log does not do this and it is not recommended that this be done on a regular basis.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 6, 2010 at 10:23 am

    #1216988

    so, it means if i do Transactional log backup ONLY , it will truncate logs, am i right ???

    and there is no need to run dbcc shrinkfile

    i read in many article shrink causes Index fragmentation too. so, i want to avoid Shrink

    so, please tell me is it a good practice to only take log backup and dont shrink log files ??

    unless it is necessary

  • September 6, 2010 at 10:26 am

    #1216991

    Correct.

    Have a look at the output of:

    DBCC SQLPERF(logspace)

    Before and after a log backup

Viewing 5 posts - 1 through 4 (of 4 total)

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