Reclaiming Transaction Log Allocation Space 1

[SKent]

I am using SQL 2000. I have a database the is over 25GB and a transaction log allocation space of over 11GB. The transaction log has been truncated after a backup and it is only 294MB comparatively ... how do I reclaim the 11GB of allocated space on the server. I am getting real close to running out of pyhsical drive space ...

 

[JayKusch]

try

BACKUP LOG DatabaseName WITH NO_TRUNCATE

Thanks

J. Kusch

 

[RobertT687]

If JayKush's suggestion doesn't shrink the file, try this.
Its a bit lengthy. Not sure of the author, I found it somewhere on the net, so I take no credit for it.

Substitute your specific info in place of 'yourdatabasename'.

I use it frequently.

SET NOCOUNT ON

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

-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
/* -- Use sp_helpfile to identify the logical file name that you want to shrink. */
/* This is the name of the database for which the log will be shrunk. */
USE [yourdatabasename] -- Your database name
SELECT @LogicalFileName = 'yourdatabasename_Log', -- The Logical name of the LOG file
@MaxMinutes = 2, -- Limit on time allowed to wrap log. Keeps it from running forever.
@NewSize = 1 -- target size in MB

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
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

 

[SKent]

I understand everything is systaxed exactly the same, except the database name ... correct ?????

 

[RobertT687]

Right.
Just change those names.
I usually have to run it 3 or 4 times in a row to get the maximum 'shrinkage'. Don't forget to perform a full database backup when done.

 

[SKent]

OK thanks .. I'm in a sql class right now so I will have to wait until Monday to work on this .. I'll let you know

 

[NortonES2]

Run a complete backup of the database (just in case). Then detach the database, delete the transaction log and reattach the database. This will create a new transaction log for you.

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

If it doesn't leak oil it must be empty!!