Post reply

reduce the physical log size

  • August 23, 2010 at 10:34 pm

    #148541

    Hi

    I use the following code to delete the oldest 4 years in the last 5 years from production db

    DECLARE @daysinthepast INT;

    DECLARE @counter INT;

    SET @daysinthepast =1825 ;-- 5 years

    SET @counter = 1;

    WHILE (@daysinthepast > 380)

    BEGIN

    begin transaction

    print @daysinthepast ;

    print @counter ;

    SET @daysinthepast = @daysinthepast-7;

    SET @counter = @counter + 1;

    delete from orders where deliverydate < getdate() - @daysinthepast ;

    commit transaction;

    WAITFOR DELAY '00:00:02' ;

    END;

    GO

    the log files increase from 1M to 1.5GB

    i am trying to reduce the physical log size

    i am using Shrink from the enterprise manager , it says only 70M is used , i click an resize it to 80 M

    it says successful , but still it is physically 1.5 G

    i tried backng up the db , still the log file is 1.5 G

    i tried to backup the log , still the log file is 1.5 G

    any way to physically reduce the log file?

    Thanks

  • August 23, 2010 at 10:57 pm

    #1210785

    take a log backup again and then issue the shrinkfile

  • August 23, 2010 at 11:22 pm

    #1210789

    bassem_farouk (8/23/2010)

    any way to physically reduce the log file?

    Why do you need to?

    The log should be sized for your workload and log backup frequency and left alone.

    Please read through this - Managing Transaction Logs[/url]

    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

  • August 24, 2010 at 7:07 pm

    #1211398

    Hi

    Thanks for the 2 rplies , i just trying to do a proof of concept

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

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