DUMPING AND TRUNCATING THE TRANSACTION LOG

[Guest_imported]

Hello all,

I have created a maintenance plan to back up the transaction logs on an hourly basis for one of our financial systems.

The transaction log is backing up fine, however,
my understanding was that sql would also take care of truncating it at the same time without voiding the integrity of the logs.

I need Answers? Please shed some light on this issue.

Thankyou,

 

[tlbroadbent]

SQL Server truncates the inactive portion of the log after a backup. See SQL BOL for details.

Transaction Log Backups

http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_565v.asp

Are you having a specific problem with the backups? One common misunderstanding is that truncating inactive transaction means reducing the log file size. This isn't the case. SQL simply removes the transactions and reuses the space. To recover disk space you must shrink the log file. See faq183-1534 - "Shrinking Databases and Logs - SQL 7 and Higher" for more information on this topic. Terry L. Broadbent - DBA
Computing Links:

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

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[JUDGE27]

The following script, will shrink either the log or database file.

If in doubt, try it on you test environment before hand.
Remove comments that return line.Here you go,


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

-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [db_name_here] -- This is the name of the database
-- for which the log will be shrunk.
SELECT @LogicalFileName = 'logical_name_here', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 50 -- 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