Results 1 to 5 of 5
  1. #1
    Join Date
    May 2001

    Unanswered: Giant LDF file How Do I shrink it

    I have a very large .LDF file and I was wondering if there is an easy way in SQL 2000 to shrink this.


  2. #2
    Join Date
    Dec 2001
    Toronto, Canada
    Have you looked at:

    1) DBCC SHRINKDATABASE statement.
    2) DBCC SHRINKFILE statement referencing the log file.

  3. #3
    Join Date
    Oct 2001
    I usually do it by sp_detach_db - rename the .ldf file then sp_attach_db. It will create a new ldf and you don't have to worry about active transactions being at the end of the file.

    Whatever you do take a backup first.

  4. #4
    Join Date
    Dec 2001

    shrinking transaction log TSQL

    DECLARE @LogicalFileName sysname,
    @MaxMinutes INT,
    @NewSize INT

    -- Your criteria here.
    USE Pubs -- This is the name of the database for which the log will be shrunk.
    SELECT @LogicalFileName = 'pubs__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 = 20 -- in MB enter this value

    -- 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 ***'

    - Check to see if the log has shrunk from its original size.

    - Repeat the preceding steps if necessary. If the log is not shrinking,
    re-check the summary at the top of the article to see if you are encountering
    any of the common issues with shrinking the log.

    After the log shrinks:

    1. Perform a full database backup of the master database.

    2. Perform a full database backup of the user database. This is necessary
    because the SHRINK command is not logged and invalidates future transaction
    log backups unless a full database backup is completed.

    To determine why the log is growing so big in the first place, you can check for
    open transactions, long running transactions, unreplicated transactions, or
    transactions that touch a lot of data.

  5. #5
    Join Date
    Dec 2001

    Thumbs up

    No, no, no. And NO!

    Check the database properties in enterprise manager.
    On the options tab, look at the "recovery" option.

    If you have it set to "full," then the log will never shrink by itself, as you have implicity said somthing like "I want to use the log to resore a database."

    If you have it set to "Bulk-logged" then you are imlying "Don't shrink the log untill I've backed it up."

    If you have it set to "Simple" then the log will auto shrink as each transaction completes. Note: when using the simple recorory method, only an MDF backup can recorve a database, and once transactions complete, you got no chance of rolling them back.

    What do I use??? Simple. Straight up.
    Last edited by stein; 01-02-02 at 15:17.

Posting Permissions

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