Post reply

How do I reduce or eliminate logging?

  • July 14, 2008 at 11:47 am

    #190677

    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.

  • July 14, 2008 at 12:07 pm

    #842604

    Change the database mode to "Simple".

  • July 14, 2008 at 1:20 pm

    #842651

    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).

  • July 14, 2008 at 1:26 pm

    #842656

    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.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • July 14, 2008 at 1:32 pm

    #842661

    Thanks Jack, that helps.

  • July 14, 2008 at 11:14 pm

    #842804

    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.


    CDBA

  • July 14, 2008 at 11:25 pm

    #842808

    take backup of T log with this option.

    WITH TRUNCATE_ONLY

  • July 15, 2008 at 1:18 am

    #842848

    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

  • July 15, 2008 at 5:33 am

    #842966

    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

    🙂

  • July 15, 2008 at 5:43 am

    #842975

    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

  • July 15, 2008 at 6:07 am

    #842996

    thanks Gail!! very helpful info!

  • July 16, 2008 at 12:33 am

    #843536

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

  • July 16, 2008 at 1:07 am

    #843549

    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

  • July 16, 2008 at 1:13 am

    #843554

    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.:)

  • July 16, 2008 at 1:19 am

    #843561

    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