Transaction log truncation

  • Hi

       I have a huge transaction log. I am not able to truncate the same.

       I have run "DBCC SHRINKFILE" multiple times with no effect.

       Recently when going through the forums I had come across a script which truncates the transaction log. I am not able to find the same. Could somebody please repost the script.

    I know I know My search skills are pretty bad

     

     

    Rgds

    Kudla

  • CHECKPOINT

    GO

    BACKUP LOG YourDatabase WITH TRUNCATE_ONLY

    CHECKPOINT

    GO

    USE YourDatabase

    GO

    EXEC sp_helpfile

    One you have run the above you should be able to use your DBCC SHRINKFILE on the files listed.

  • If you can't use TRUNCATE_ONLY Option

    You may want to run the following command instead:

    BACKUP LOG [Databasename] WITH NO_LOG

    The NO_LOG clause removes the inactive part of the log without making a backup copy of it, and saves space by not logging the operation.

  • I have used this script many times and it works good. (I think I found it here on sqlservercentral).

    /Joel

     

    SET NOCOUNT ON

       DECLARE @LogicalFileName sysname,

               @MaxMinutes INT,

               @NewSize INT

       -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***

       USE     [Test DB]              -- This is the name of the database

                                      -- for which the log will be shrunk.

       SELECT  @LogicalFileName = 'Test DB Log',  -- Use sp_helpfile to

          -- identify the logical file

          -- name that you want to shrink.

               @MaxMinutes = 10,      -- Limit on time allowed to wrap log.

               @NewSize    = 10       -- in MB

       -- Setup / initialize

       DECLARE @OriginalSize int

       SELECT @OriginalSize = size -- in 8K pages

         FROM sysfiles

         WHERE name = @LogicalFileName

       SELECT 'Original Size of ' + db_name() + ' LOG is ' +

               CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +

               CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'

         FROM sysfiles

         WHERE name = @LogicalFileName

       CREATE TABLE DummyTrans

         (DummyColumn char (8000) not null)

       -- Wrap log and truncate it.

       DECLARE @Counter   INT,

               @StartTime DATETIME,

               @TruncLog  VARCHAR(255)

       SELECT  @StartTime = GETDATE(),

               @TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'

       -- Try an initial shrink.

       DBCC SHRINKFILE (@LogicalFileName, @NewSize)

       EXEC (@TruncLog)

       -- Wrap the log if necessary.

       WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired

             AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  -- the log has not shrunk   

             AND (@OriginalSize * 8 /1024) > @NewSize  -- The value passed in for new size is smaller than the current size.

         BEGIN -- Outer loop.

           SELECT @Counter = 0

           WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

             BEGIN -- update

               INSERT DummyTrans VALUES ('Fill Log')  -- Because it is a char field it inserts 8000 bytes.

               DELETE DummyTrans

               SELECT @Counter = @Counter + 1

             END   -- update

           EXEC (@TruncLog)  -- See if a trunc of the log shrinks it.

         END   -- outer loop

       SELECT 'Final Size of ' + db_name() + ' LOG is ' +

               CONVERT(VARCHAR(30),size) + ' 8K pages or ' +

               CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'

         FROM sysfiles

         WHERE name = @LogicalFileName

       DROP TABLE DummyTrans

       PRINT '*** Perform a full database backup ***'

       SET NOCOUNT OFF

     

  • Backing up the transaction log shrinks the active portion (I believe - I'm new at this). Have a regular t-log backup in your maintenance plans.

     

  • If you need this disk space urgently, you can use a rather brute-force method.

    1. detach db

    2. rename or delete log file

    3. attach db

    SQL Server gives a warning but generates a nice new log file. Just make sure, you have a most current backup of that db and don't make this your usual procedere.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • This is the good old procedure from SQL 7.0. It works great.

    create proc sp_force_shrink_log   

    /*   

    *************************************************************   

    Name: sp_force_shrink_log   

    Description:   

      Shrink transaction log of the current database in SQL Server 7.0.   

    Switch context to proper db to execute.   

    Usage: exec sp_force_shrink_log <target_percent>, <target MB>, <iterations>, <backup options>   

       exec pubs..sp_force_shrink_log   

       

    Author:  Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000   

       zanevsky@azdatabases.com   

       

    Input Params:   

    -------------   

    @target_percent  tinyint. default = 0. Target percentage of remaining shrinkable   

          space. Defaults to max possible.   

    @target_size_MB  int. default = 10. Target size of final log in MB.   

    @max_iterations  int. default = 1000. Number of loops (max) to run proc through.   

    @backup_log_opt  nvarchar(1000). default = 'with truncate_only'. Backup options.   

       

    Output Params:   

    --------------   

       

    Return:   

       

    Results:   

    ---------   

       

    Locals:   

    --------   

    @err  Holds error value   

       

    Modifications:   

    --------------   

       

    *************************************************************   

    */   

        @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(25)   

    )   

    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, 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  @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, 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

  • This worked for me many times.

    dbcc shrinkfile (2, notruncate)

    dbcc shrinkfile (2, truncateonly)

    create table t1 (char1 char(4000))

    go

    declare @i int

    select @i=0

    while(@i < 100)

    begin

    insert into t1 values ('a')

    select @i = @i +1

    end

    truncate table t1

    backup log <db_Name> with truncate_only

    drop table t1

    go

  • What I have found to be a quick fix would be to run a backup of the transaction log, do a full backup of the DB.  This  can be done in Ent. Mgr.

    Once that is complete, from query analyzer...

    dbcc shrinkfile (filename_data, 500) 

    The filename_log is the name of the log file (which you can find by checking the properties of the Database in Ent. Mgr.) The 500 is a size to shrink to.

    Once you have successfully run that you will need to do a transaction log backup and another full backup to clear the space.  Be sure to overwrite and not append or your backup can be huge.

    You may have to do this entire procedure a couple of times to get the size down.

    I hope this helps!

Viewing 9 posts - 1 through 8 (of 8 total)

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