June 22, 2004 at 10:26 am
Hi All,
We are having a problem with our backups. When we a full backup the transaction is not being truncated. I have DBCC trancount and there are no active transactions on the DB.
Any ideas?
Thanks,
Stuart
June 22, 2004 at 11:14 am
A full database backup does not truncate the transaction log. Log truncates only occur when a transaction log backup is done. The alternative would be to set the recovery model to simple, which will cause the transaction log to be truncated with each "checkpoint" that is done (this usually isn't recommended for a production database).
Steve
June 22, 2004 at 11:16 pm
Here's something I got from the Microsoft site -- Works pretty well(Sorry all the indenting goes out of it with this post. Get the original off the MS site if you want -- see note):
/*=========================================================================
Title: Shrink SQL Server Transaction Log
Script SQL_Server_Trx_Log_Shrink.sql
Purpose: INF: How to Shrink the SQL Server 7.0 Transaction Log
Create/Update History:
5/13/2004 10:47:40 AM - G Milner: Created;
Notes:
5/13/2004 10:47:40 AM - G Milner: Taken from MS site:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q256650
Microsoft Knowledge Base Article - 256650 ;
=========================================================================*/
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** check out the database file names:
-- sp_helpdb MyDBName
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE MyDBName -- This is the name of the database
-- for which the log will be shrunk.
SELECT @LogicalFileName = 'MyDBName_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@MaxMinutes = 2, -- 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
/* to shring the database file (optional) */
-- DBCC SHRINKFILE (MyDBName_Data,1000)
G. Milner
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply