Reduce log file to minimum size

Question

Post reply

Reduce log file to minimum size

fasttrack

SSCertifiable

Points: 6071
More actions
September 6, 2006 at 8:56 am

#60765

Hi,
I have a database with a large transaction log, I run the shrink log file using the tool in Enterprise Manager.
After the shrink was completed, the size is still too large, It was reduced only some MB.
How can I riduce the size of Log file to small size.
Thank

Viewing 7 posts - 1 through 6 (of 6 total)

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

1.Check to see if there are any long running xcation (DBCC OPEN TRAN)

2.Take a full backup and then Run DBCC ShirnkDatabase.

3.Or WORST case secnario: change the recovery model to "SIMPLE", then take a full backup and then Run DBCC ShirnkDatabase and revert the recovery model.

Thanks

Sreejith

Greg Milner SSCrazy Points: 2425 More actions · Answer 2

Put this in your master database and then run it from whatever database you want to shrink the log in. See the Example Usage in the header comments.

Be sure to do a full backup of your DB right after you run it so your log can start working properly again.

Got the guts of the proc from: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q256650

Cheers.

Here's the code:

create procedure sp_trx_log_shrink (@MaxMinutes INT,
                                    @NewSize INT      )

/*=========================================================================
Title:               Shrink SQL Server Transaction Log Stored procedure
Script               sp_trx_log_shrink.sql

Purpose:             system proc based on 
                     INF: How to Shrink the SQL Server 7.0 Transaction Log  
                     Force shrink transaction log of current database to 
                     specific size.               

Params:         @MaxMinutes = Max number minutes to run before stoppint 
                                (recommend 2 at least)
                @NewSize = New size in MBs of the log file after shrinking
                                (recommend at least 10 MB in most DBs)

Example Usage:       EXEC sp_trx_log_shrink 2, 200
                    (shrink for 2 min., leaver trx log at 200 MB)


Create/Update History: 
    10/31/2005 9:38:24 PM - GMilner: Pre-delete DummyTrans table if exists.
    3/9/2005 3:33:44 PM - GMilner: Converted to procedure.

Notes: 
    Assumes only 2 physical database files and that _Data file
        is file id 1 in sysfiles table and that log file is file id 2.
    Original Source: 
        http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q256650
        Microsoft Knowledge Base Article - 256650 ;
=========================================================================*/
AS 
SET NOCOUNT ON
DECLARE @err int
DECLARE @LogicalFileName sysname
--DECLARE @SSQL as VARCHAR(255)
DECLARE @DBN  as nVarchar(50)

-- Setup / initialize
DECLARE @OriginalSize int
set @DBN = (select db_name()) 
PRINT 'Database: ' +  @DBN


SET @LogicalFileName = (SELECT FILE_NAME (2))

PRINT 'Log logical filename: ' + @LogicalFileName
PRINT ''
EXEC sp_helpdb @DBN

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

IF OBJECT_ID('DummyTrans') IS NOT NULL 
DROP TABLE DummyTrans

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    
      -- IF thhe value passed in for new size is smaller than the current size...
      AND (@OriginalSize * 8 /1024) > @NewSize  
  BEGIN -- Outer loop.
    SELECT @Counter = 0
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
      BEGIN -- update
        -- Because it is a char field it inserts 8000 bytes...
        INSERT DummyTrans VALUES ('Fill Log')  
        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

IF @err  0 
    BEGIN
        RETURN(@err)
        PRINT '*** SHRINK FAILED!!! ***'  
    END

IF @err = 0
    BEGIN 
        PRINT '*** Perform a full database backup ***'            
    END

G. Milner

Greg Milner SSCrazy Points: 2425 More actions · Answer 3

Also, if you haven't already, you should set your log autogrow to a certain MB size per growth rather than a percent. If you have a 10 GB database and you have your log set to 10% growth, it will grow by 1GB the first time, 1.1+ GB the next time, etc. You get the picture.

Much more controllable if it grows by, say, 100 MB hard coded as 100 MB per growth.

G. Milner

fasttrack SSCertifiable Points: 6071 More actions · Answer 4

First of all: thank to everybody for your useful suggestions.

A question,

this problem is from a DB that I do not manage, I was only asked some help.

I have found out that transaction log is set as :

"Restricted file growth"

instead of : "UNRestricted file growth".

"Restricted file growth" may affect the shrink operation on log DB?

Shawn Wilson Ten Centuries Points: 1271 More actions · Answer 5

run this and let us know the last time your transaction log was backed up.

SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),

MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,

ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate

FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A

ON A.database_name = B.name AND A.type = 'L' GROUP BY B.Name ORDER BY B.name

run DBCC PERFLOG and let us know the size and percent full of the log.

run select * from master..sysdatabases where category = 1 this will let us know if your database is marked for replication.

Give us this info and we can see where we can go from here.

fasttrack SSCertifiable Points: 6071 More actions · Answer 6

thank for everything,

turning on simple recovery, shrink and then to turn back, log file was reduced to small size.

Now,

I will try the code posted above ....