HOW DO I SHRINK SQL TRANSACTION LOGS ??? 1

[jschaddock]

I have a number of databases contained on the SQL server that I administer.Looking at the properties of some of the databases they have huge amounts of unused space on their transaction logs which I would like to free up to the system whilst leaving the space that is used by the transaction log alone.I have tried using the dbcc shrinkfile command but can't seem to get it to work.

Could someone please let me know how I can free up the unused space on the transaction log whilst leaving the used space alone?

Thanks

jschaddock

 

[oldduffer]

Mr Haddock

There are something called DBCC's which have a lot of in-built management functionality, use the following to shrink the size of a database or log file. Look in books on line for more information

hope this helps


Checkpoint
go
DBCC SHRINKfile (yourfile)

 

[mfairchild]

SQL Server log files are "wrapping" logs, so shrinking them doesn't shrink it as much as you'd like. The code below is from the MS knowledge base. There's an article in there that explains the whole process (I don't know the article# off of the top of my head). I've run this code and it works well. On extremely large logs, you may have to run it twice. Remember! Backup everything first!

Good luck.


SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT,
@OriginalSize int,
@Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)


-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [MN_Kids] -- This is the name of the database for which the log will be shrunk.
SELECT @LogicalFileName = 'MN_Kids_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 = 10 -- in MB

-- Setup / initialize
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.
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

 

[tlbroadbent]

Check out faq183-1534. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.