Trans Log not Truncating

  • Have a semi large database (about 15 gig data file) which generates quite a large Log file. I do transaction log backups twice a day but it seems quite a bit of space, about 7/8 gig won`t truncate from the log.

    Is there any reason behind this, or any way to get rid of it, short of recreating the log file from scratch?

    Andy.

  • How are you backing up the transaction log?

    Has it ever been small?

    You can shrink the file, have a look at "shrinking transaction logs" in BOL to see a explanation of the log file

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • Which version of SQL are you using. If 7 then you will need help killing the virtual logs inside the TL.

    See http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=26&CategoryNm=Maintenance%20and%20Management%20&CategoryID=1 for a great script to do this.

    However I do suggest only do this when you are going to do a full backup afterwards.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • If you do not care about the log data, do the steps in this order

    1) "backup log" with truncate_only.

    2) Then Shrink the log

    An example below.

    This will bring your logs to the original size when first created. I use these commands frequently and it works.

    =====================================

    use MY_DATABASE

    go

    backup log MY_DATABASE with TRUNCATE_ONLY

    dbcc shrinkfile(2, 0)

    go

    ======================================

    -- Chandra Cheedella

  • I think you misread my posting.

    I said I backed up the logs twice a day, that has no effect. Have tried shrinking manually, has no effect either.

    It`s all in 2000.

    Andy.

  • Did you try setting up the recovery model to simple under properties, options. Simple recovery model in 2000 is akin to truncate log on check point option in Sql 7.0.

  • Andy

    Have you tried the script above ...it does what it says on the tin...We have used it to great effect in similar situations

    Tim

Viewing 7 posts - 1 through 6 (of 6 total)

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