Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: How to shrink a tranlog which won't shrink

    If you have problems shrinking a transaction log no matter what commands you issue, here's a way to shrink the tranlog:

    1. Right click on the properties of the database and go to the options tab.
    2. Change the recovery mode to simple.
    3. Right click again on the database - go to all tasks - shrink database.
    4. Shrink the database.
    5. Change the recovery mode back to what you had it as.

    I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280

    Know what your doing!!!

    If your database is in FULL recovery mode this little trick wil break your backup chain!!! You will not be able to do a point-in-time restore (which is the reason you're having your database on FULL) to a moment after you set the db to SIMPLE.

    When you do this, make a full backup inmediately afterwards.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    An alternate method is to take a tran log backup after the initial attempt to shrink the file. This should wrap the active portion of the log back to the beginning of the file. So, if you have a runaway transaction log:

    1) Backup the log
    2) Backup the log again (yes again)
    3) dbcc shrinkfile.

  4. #4
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    If after doing all the backing up and dbcc shrinkfile STILL doesn't work try
    dump tran DATABASENAME with no_log

    If this doesn't help then your log file might be corrupt. Not being able to shrink the log file past a certain point might be a sign of a corrupt log file.

    Good luck,
    Hope this helps

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Giving credit where credit is due, I found this code by Andrew Zanevsky about 5 years ago. It was originally for SQL 7 and has been modified for SQL 2K. I can only remember a few times where it has not done the job.

    1st ... execute the code
    2nd ... change to the db in which to shrink the log and execute sp_force_shrink_log

    Code:
     
    use master
    go
    if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
    go
    create proc sp_force_shrink_log
    --------------------------------------------------------------------------------
    -- 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
    /*
    FileId      FileSize               StartOffset            FSeqNo      Status      Parity CreateLSN                   
    ----------- ---------------------- ---------------------- ----------- ----------- ------ --------------------------- 
    2           1245184                8192                   925963      0           128    0
    */
    create table #loginfo ( 
        id          int identity, 
        FileId      int, 
        FileSize    numeric(22,0), 
        StartOffset numeric(22,0), 
        FSeqNo      int, 
        Status      int, 
        Parity      smallint, 
        CreateLSN   varchar(32)
    )
    create unique clustered index loginfo_FSeqNo on #loginfo ( 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, CreateLSN ) 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  @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 #logfiles 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, CreateLSN ) 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
    Last edited by tomh53; 01-10-07 at 09:45.

    -- This is all just a Figment of my Imagination --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •