February 1, 2005 at 6:22 pm
Hi,
SQL 2000 sp3a. (760)
I have set the recovery mode as simple for one of my databases.
But, my transaction log is still growing for the database.
I have set an alert to truncate the transaction log when it reaches 80%.
DBCC opentran will not show long running transactions. (There are idle periods)
Transaction log size 6gb. Total tr. log size is 10gb.
If not for the alert transaction log will auto grow.
As I understand inactive portion of the transaction log should be reused on checkpoints.
What's going wrong? Please help.
Thanks,
Warna
February 2, 2005 at 10:37 pm
I got this off a MS site a while back and use it every week to force shring the transaction logs on all my large DBs. It's set up here to do the TempDB, but you can change it to do any database. This is the only thing that consistently works. I use it as a SQL job. Note, you must do a full database backup afterward to regain transaction log contiguity and hence recoverability.
/*=========================================================================
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 tempdb
--
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE tempdb -- This is the name of the database
-- for which the log will be shrunk.
SELECT @LogicalFileName = 'templog', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@MaxMinutes = 2, -- Limit on time allowed to wrap log.
@NewSize = 25 -- 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 shrink the database file (optional) */
-- DBCC SHRINKFILE (tempdev,300)
G. Milner
February 3, 2005 at 6:51 am
Thanks, Milner.
Is this a known bug with SQL 2000 running sp3a?
Warna
February 3, 2005 at 7:39 am
No. It has alwasy been this way in SQL 2000 (and SQL Server 7, for that matter). Shrinking the Transaction Log dynamically (without, say, detaching the database, deleting the ldf, and reattaching) has always been a challenge. This script, scheduled as a job, pushes all the information in the log to the "front" of the file before compressing the file with DBCC SHRINKFILE. This is the only way to do it.
Tip: make sure your database Recovery Mode is NOT set to "full" unless you absolutely need that. Use bulk logged instead.
See the KB articles for more technical info:
http://support.microsoft.com/kb/q256650/
or even better yet:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/kb/317375
G. Milner
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply