Post reply

Trans Log not Truncating

  • October 22, 2002 at 2:19 am

    #58133

    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.

  • October 22, 2002 at 3:24 am

    #439096

    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

  • October 22, 2002 at 5:28 am

    #439107

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

    See http://qa.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)

  • October 22, 2002 at 8:13 am

    #439125

    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

  • October 22, 2002 at 9:02 am

    #439133

    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.

  • October 22, 2002 at 9:06 am

    #439136

    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.

  • October 22, 2002 at 5:06 pm

    #439165

    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