locked
how can i shrink a log file, when it's full? RRS feed

  • Question

  • Hello i'm quite new of sql server 2005.

     

    Can any of you help me with this topic?

     

    Sql warms me that the log file for the database DB1 it's full, i've tried to shrik it, with the followin T sql

     

    but the problem persists

     

     

    DBCC SHRINKFILE (dm1_log, 10000) WITH NO_INFOMSGS

     

    _____________________________________________________________________________________

     

    SET NOCOUNT ON

    DECLARE @LogicalFileName sysname,

    @MaxMinutes INT,

    @NewSize INT

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

    USE db1 -- This is the name of the database

    SELECT @LogicalFileName = 'dm1_log', -- Use sp_helpfile to identify the logical file name that you want to shrink.

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

    @NewSize = 1000 -- 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

     

    Monday, July 28, 2008 1:47 PM

Answers

  • If you are mirroring your database, then you should avoid the above code, it will break your log chain.  The proper way to maintain log size is to take regular LOG Backups.  If you are in full recovery, then you should be taking at least 2 hr log backups to allow the logs to truncate and the space to be reused.  If you are not mirroring the databases, and you don't want to take frequent Log backups, then change the recovery model of the database from FULL to SIMPLE recovery, and the log will auto truncate as transactions commit.

     

    Monday, July 28, 2008 2:49 PM
  • To keep you log file to a manageable size you have several options.  If the database recovery model is set to full or bulked then schedule a log file backup.  This will truncat the log.  You can also set the recovery model to simple recovery, whicj will prohibit you from creating a log backup, but will keep the log size in check.

     

     

    Monday, July 28, 2008 2:57 PM
  • Hi,

     

    As mentioned above from experts is true, there is one way around if you dont want any log backups to store by any reason.

     

    Try to execute this command it will truncate the T log and shrink the log file size need to execute

    Simultaneously dont forget to use the Database name.

     

    backup log 'yourdatabasename' to disk ='D:\DFS_demographics.trn'

    go

    dbcc shrinkfile (yourdatabase_logfilename,1)

     

    ----------------------------------For database log file name try to execute sp_helpdb 'Yourdatabasename'

     

    BR

    Praveen

     

    (If you find any issue do not hessitate to post,if log file size not redusing means log file might be working with uncommited transaction try after few minutes)

    Monday, July 28, 2008 11:52 PM
    Answerer
  •  

    As per your need schedule it in Agent job.

     

    BR

    Praveen

     

    Mark as answer if helps

    Tuesday, July 29, 2008 7:48 AM
    Answerer

All replies

  • If you are mirroring your database, then you should avoid the above code, it will break your log chain.  The proper way to maintain log size is to take regular LOG Backups.  If you are in full recovery, then you should be taking at least 2 hr log backups to allow the logs to truncate and the space to be reused.  If you are not mirroring the databases, and you don't want to take frequent Log backups, then change the recovery model of the database from FULL to SIMPLE recovery, and the log will auto truncate as transactions commit.

     

    Monday, July 28, 2008 2:49 PM
  • To keep you log file to a manageable size you have several options.  If the database recovery model is set to full or bulked then schedule a log file backup.  This will truncat the log.  You can also set the recovery model to simple recovery, whicj will prohibit you from creating a log backup, but will keep the log size in check.

     

     

    Monday, July 28, 2008 2:57 PM
  • Hi,

     

    As mentioned above from experts is true, there is one way around if you dont want any log backups to store by any reason.

     

    Try to execute this command it will truncate the T log and shrink the log file size need to execute

    Simultaneously dont forget to use the Database name.

     

    backup log 'yourdatabasename' to disk ='D:\DFS_demographics.trn'

    go

    dbcc shrinkfile (yourdatabase_logfilename,1)

     

    ----------------------------------For database log file name try to execute sp_helpdb 'Yourdatabasename'

     

    BR

    Praveen

     

    (If you find any issue do not hessitate to post,if log file size not redusing means log file might be working with uncommited transaction try after few minutes)

    Monday, July 28, 2008 11:52 PM
    Answerer
  • Thanks for your suggestion.

    I've found out a code to shrink and back up log file.

     

    USE DB1

    GO

    DBCC SHRINKFILE(DM1_LOG, 1)

    BACKUP LOG DB1 WITH TRUNCATE_ONLY

    DBCC SHRINKFILE(DM1_LOG, 1)

     and it's temporary solved my problem.

     

    Now, how Can i schedule back up log file to permanently fix it.....

     

    is it under: database/properties/transaction log shippings?!

    Is it my understanding correct?

     

     

     

    Tuesday, July 29, 2008 7:41 AM
  •  

    As per your need schedule it in Agent job.

     

    BR

    Praveen

     

    Mark as answer if helps

    Tuesday, July 29, 2008 7:48 AM
    Answerer
  • If you are mirroring your database, then you should avoid the above code, it will break your log chain.  The proper way to maintain log size is to take regular LOG Backups.  If you are in full recovery, then you should be taking at least 2 hr log backups to allow the logs to truncate and the space to be reused.  If you are not mirroring the databases, and you don't want to take frequent Log backups, then change the recovery model of the database from FULL to SIMPLE recovery, and the log will auto truncate as transactions commit.

     


    Mr jonathan ı have a bigf problem like Marco's ı havent any space at my hdd and ı couldnt shrink the log file and ı have to do this immediately because all of the pc at company have to log in all days may you answer me
    Monday, May 4, 2009 7:33 AM