How do I reduce or eliminate logging?

  • I have a production server where I really need no logging for the purpose of recovery. We currently have a nightly job that shrinks the log file, but I am wondering if there is a better way to do this. Is there a way to turn off logging globally or at the transaction level? It seems this would improve performance.

    While my example is a production box, it seems that the solution to this would apply to development environments where extra logging overhead is not needed.

  • Change the database mode to "Simple".

  • Thanks Nathan, however, my database is already in the simple recovery mode. The simple mode does not eliminate or reduce logging (which is the goal in my case).

  • There is no way to eliminate logging. SQL Server writes changes to the Log and then to the database from the log. Changing the recovery mode to simple will cause the log to truncate on checkpoints, thus keeping the transaction log from growing.

  • Thanks Jack, that helps.

  • If you are sure that you do not need log backups (that means you will have only restore capability till last full/differential backup if you implement this approach), use the below command to truncate the log.

    USE

    BACKUP LOG WITH TRUNCATE_ONLY

    You can schedule this piece of code as a job and schedule it for frequent execution.

  • take backup of T log with this option.

    WITH TRUNCATE_ONLY

  • shahbaz.oradba (7/14/2008)


    take backup of T log with this option.

    WITH TRUNCATE_ONLY

    In simple recovery mode (which the OP's database is in), the log automatically truncates on checkpoint. Hence there is no need to specify a truncate of the log

    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
  • BACKUP LOG WITH TRUNCATE_ONLY

    --->Can some one please tell me what does the above statement do. I understand that the log files are backuped up

    My questions are

    1) where is this backup stored? (I mean BACKUP LOG)

    2) what is truncate_only? (does it only truncate the log files?)

    3) Why do we take a backup of the log and then truncate ?

    4) Why cant we truncate the log directly ?

    thanks

    🙂

  • rinu philip (7/15/2008)


    My questions are

    1) where is this backup stored? (I mean BACKUP LOG)

    It's not.

    2) what is truncate_only? (does it only truncate the log files?)

    Discard the inactive portion of the log without backup it up

    3) Why do we take a backup of the log and then truncate ?

    You're not. You're just truncating it. No backup is created

    4) Why cant we truncate the log directly ?

    that's exactly what you're doing.

    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
  • thanks Gail!! very helpful info!

  • In this case instead of trncating the logfile if i'll shrink the log file what will happen?

  • Abhijit (7/16/2008)


    In this case instead of trncating the logfile if i'll shrink the log file what will happen?

    Provided there's unused space within the log file, the size of the file on disk will reduce.

    The shrink will not discard tran log records

    Just for completeness, shrink is not recommended, especially if run regularly as the log file will probably grow again, the process of grow and shrink will consume resources unnecessarily and the repeated shrink/grow may well cause fragmentation at the file level.

    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
  • Hi, I think you could use the DBCC SHRINKFILE , this shrinks the log file by removing as many virtual log files, that the files that are not in use. After shrinking the log then a trucate will get more space.:)

  • rinu philip (7/16/2008)


    After shrinking the log then a trucate will get more space.:)

    Explain what you mean there please?

    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

Viewing 15 posts - 1 through 15 (of 21 total)

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