SQL Server Performance

Resize LDF

Discussion in 'Performance Tuning for DBAs' started by fauzanf, Oct 1, 2009.

  1. fauzanf New Member

    Hello My Friends,
    I'm newbie in SQL... [:p]
    I have one database, the size of MDF file is only 21 MB. But The LDF File is almost 200 MB.
    Why...? Is there any idea to reduce the LDF file???
    What will happen if the LDF file is reduce ...?
    Thanks very much and Regards,
    Fauzan
  2. FrankKalis Moderator

    Read about taking care of the log file in Books Online.
    The log file may growth for several reasons and there is no need to worry about this at all as long as you take care of it. Amongst other things the log records all information for data modification operations between two backups. So, if you have an active database with a lot of modifications, the log will growth to accomodate for this. If this happens regularly, I would resize the log to a reasonable size + some buffer, because every file growth operation is quite expensive in that it takes time, leads to file fragmentation and may slow down overall performance. There is no point in shrinking the file just to free up some disk space, because you may sacrifice good performance just for some extra MB's. If you are that short of disk space, you would probably want to go for more disk space.
  3. ghemant Moderator

  4. ravi0207 New Member

    Dear Fauzan
    Create this stored procedure(Author Andrew) and run it regulary as and when you want to shrink log file
    CREATE proc sp_force_shrink_log
    --------------------------------------------------------------------------------
    -- modified for SQL Server 2000
    -- Purpose: Shrink transaction log of the current database in SQL Server 7.0.
    -- Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
    -- zanevsky@azdatabases.com
    --------------------------------------------------------------------------------
    @target_percent tinyint = 0,
    @target_size_MB int = 10,
    @max_iterations int = 1000,
    @backup_log_opt nvarchar(1000) = 'with truncate_only'
    as
    set nocount on
    declare @db sysname,
    @last_row int,
    @log_size decimal(15,2),
    @unused1 decimal(15,2),
    @unused decimal(15,2),
    @shrinkable decimal(15,2),
    @iteration int,
    @file_max int,
    @file int,
    @fileid varchar(5)
    select @db = db_name(),
    @iteration = 0
    create table #loginfo (
    id int identity,
    FileId int,
    FileSize numeric(22,0),
    StartOffset numeric(22,0),
    FSeqNo int,
    Status int,
    Parity smallint,
    CreateTime varchar(50)
    )
    create unique clustered index loginfo_FSeqNo on #loginfo1 ( FSeqNo, StartOffset )
    create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
    insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles where status & 0x40 = 0x40
    select @file_max = @@rowcount
    if object_id( 'table_to_force_shrink_log' ) is null
    exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
    insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
    select @last_row = @@rowcount
    select @log_size = sum( FileSize ) / 1048576.00,
    @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
    @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
    from #loginfo
    print 'Inizio.'
    select @unused1 = @unused -- save for later
    select 'iteration' = @iteration,
    'log size, MB' = @log_size,
    'unused log, MB' = @unused,
    'shrinkable log, MB' = @shrinkable,
    'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
    while @shrinkable * 100 / @log_size > @target_percent
    and @shrinkable > @target_size_MB
    and @iteration < @max_iterations begin
    select @iteration = @iteration + 1 -- this is just a precaution

    exec( 'insert table_to_force_shrink_log select name from sysobjects
    delete table_to_force_shrink_log')
    select @file = 0
    while @file < @file_max begin
    select @file = @file + 1
    select @fileid = fileid from #logfiles1 where id = @file
    exec( 'dbcc shrinkfile( ' + @fileid + ' )' )
    end
    exec( 'backup log [' + @db + '] ' + @backup_log_opt )
    truncate table #loginfo
    insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
    select @last_row = @@rowcount
    select @log_size = sum( FileSize ) / 1048576.00,
    @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
    @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
    from #loginfo
    select 'iteration' = @iteration,
    'log size, MB' = @log_size,
    'unused log, MB' = @unused,
    'shrinkable log, MB' = @shrinkable,
    'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
    end
    if @unused1 < @unused
    select 'After ' + convert( varchar, @iteration ) +
    ' iterations the unused portion of the log has grown from ' +
    convert( varchar, @unused1 ) + ' MB to ' +
    convert( varchar, @unused ) + ' MB.'
    union all
    select 'Since the remaining unused portion is larger than 10 MB,' where @unused > 10
    union all
    select 'you may try running this procedure again with a higher number of iterations.' where @unused > 10
    union all
    select 'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @unused <= 10
    else
    select 'It took ' + convert( varchar, @iteration ) +
    ' iterations to shrink the unused portion of the log from ' +
    convert( varchar, @unused1 ) + ' MB to ' +
    convert( varchar, @unused ) + ' MB'
    exec( 'drop table table_to_force_shrink_log' )
    GO
    Regards
    Ravi
  5. fauzanf New Member

    Hello Ravi,
    Thank you... that's very interesting...
    By the way, the procedures running and it reduce the LDF.
    But, after i execute it... there's an error.
    Server: Msg 1906, Level 11, State 1, Procedure sp_force_shrink_log, Line 36
    Cannot create an index on '#loginfo1', because this table does not exist in database 'MyDatabase'.
    Any idea...?
  6. ravi0207 New Member

    Hi Fauzan
    I guess what satya is saying is r8 you have to understand what is log file and how to shrink it properly,
    Best way to take full backup, SQL automatically shrink your log file..
    Regards
    Ravi
  7. satya Moderator

Share This Page