Binaryworld - Shrinking transaction log file on sql server 7.0 ... [ SQL Server -> Server Operations ]

Many times when I do bult insert/delete operation my transaction log grows very fast and I use this scipt to shrink it. This simple scrip will show you how to shrink transaction log file in SQL Server 7.0 when your DBCC SHRINKFILE command wont work. In most cases DBCC SHRINKFILE(,) should work but not all time it will work. If you need full explaination then goto http://support.microsoft.com/kb/q256650/

To run this script just open a new query anylyzer window copy/paste the following scipt.
- Replace with your databse name which has to be shrunk
- Replace with logical name of log file. use sp_helpdb to findout the name. Generally name should be _log where is your database name
- Change @MaxMinutes and @NewSize or leave as default

Click here to copy the following block

--//Description : The folloing script is taken from microsoft site and it workd great
--//   with sql 7.0
--//
--//Credit : Microsoft

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
      @MaxMinutes INT,
      @NewSize INT

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

USE <put_your_db_name> -- This is the name of the database for which the log will be shrunk.

SELECT @LogicalFileName = '<put_db_log_file_name>', -- Use sp_helpfile to identify the logical file
      -- name that you want to shrink.
      @MaxMinutes = 2,    -- Limit on time allowed to wrap log.
      @NewSize  = 500   -- 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

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.