Shrink DB Log File
-
Hi
I face a problem with my SQL Server 2000 database. The Log of the database keeps on growing. I tried shrinking the database log using DBCC ShrinkDatabase (MyData). But the Log size has never shrinked.
My friend adviced me take a full backup of the database and try shrinking it. But this method also not works.
I used to DTS the data from this MyData to a temp db, drop and recreate MyData and finally import the data from temp db to MyData. This task i perform once a week.
Please help me.
Thanks in advance.
S A T ...
Sathish's Blog -
If you don't backup the Log File, change the database recovery option to simple, but first time, detach you database and then attach the database with out the log file (SQL will create Log File automatecally)
-
Hi Sathish,
If u had executed the command mentioned by u shrinks the log file means free the space (old data in the tlog)
but ur problem is that u want to shrink the logfile on disk. i may be wrong.
backup log database name with truncate_only
dbcc shrinkfile(logical name of the database,size)
this will help u
sorry if i am wrong
from
Killer
-
There are sometimes problems with shrinking a database log file due to the fact that it hasn't ever been completely filled. ( I remember seeing this somewhere.)
There was a script that filled the transaction log, and then shrank to the specified size you required.
Originally it could be found at:
http://support.microsoft.com/support/kb/articles.q256/6/50.asp
If you can't find it, let me know and I'll post a copy.
-
I had a look at the path, and it no longer exists.
Here's the script:
---************************************---
/*
Shrink a named transaction log file belonging to a database
Originally found at;
http://support.microsoft.com/support/kb/articles/q256/6/50.asp
Changes:
28.08.2001
Modified the inner loop so it tested the dx time so long overruns did not happen
Modified the inner loop so it had a fixed minimum quantity so there was no skip in skip out
29.08.2001
Modified the inner loop so it had a dynamic minimum quantity to allow faster shrinkage
24.01.2002
Modified the USE statement so it uses brackets around the dbname
Modified the @TruncLog variable so it uses brackets around the dbname
31.05.2002
Modified the code to use PRINT instead of SELECT in several cases
Modified the code to use @MaxCount instead of two unclear rules
Modified the code to use @Factor instead of several hard-coded values
Commented the use of @Factor
Moved the configuration and @Counter init code to before the start of the first loop to avoid repetition
Modified the code to display the process runtime in seconds rather than minutes
*/
SET NOCOUNT ON
DECLARE @LogicalFileName SYSNAME,
@MaxMinutes INT,
@NewSize INT,
@Factor FLOAT
/*
The process has several control parameters, most of the time you only need to worry about the first four
as these are the big controls whereas the fifth is simply a fine tuning control which rarely needs to
come into play.
*/
--This is the name of the database for which the log will be shrunk.
USE [Speqs]
--Use sp_helpfile to identify the logical file name that you want to shrink.
SET @LogicalFileName = 'Speqs_Log';
--Limit on time allowed to wrap log in minutes
SET @MaxMinutes = 5;
--Ideal size of logfile in MB
SET @NewSize =100;
/*
Factor determining maximum number of pages to pad out based on the original number of pages in use
(single page = 8K). Values in the range 1.0 - 0.8 seems to work well for many databases.
Increasing the number will increase the maximum number of pages allowed to be padded, which should
force larger amounts of data to be dropped before the process finishes. Often speeds up shrinking
very large databases which are going through the process before the timer runs out.
Decreasing the number will decrease the maximum number of pages allowed to be padded, which should
force less work to be done. Often aids with forcing smaller databases to shrink to minimum size
when larger values were actually expanding them.
*/
SET @Factor = 1.0;
/*
All code after this point is driven by these parameters and will not require editing unless you need to
fix a bug in the padding/shrinking process itself.
*/
-- Setup / initialize
DECLARE @OriginalSize INT,
@StringData VARCHAR(500)
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName;
SELECT @StringData = '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;
PRINT @StringData;
PRINT ''
--Drop the temporary table if it already exists
IF ( OBJECT_ID('[dbo].[DummyTrans]') IS NOT NULL )
DROP TABLE [DummyTrans]
CREATE TABLE [DummyTrans]( [DummyColumn] CHAR(8000) NOT NULL );
-- Wrap log and truncate it.
DECLARE @Counter INT,
@MaxCount INT,
@StartTime DATETIME,
@TruncLog VARCHAR(500)
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
SET @TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY';
EXEC (@TruncLog)
-- Configure limiter
IF @OriginalSize / @Factor > 50000
SET @MaxCount = 50000
ELSE
SET @MaxCount = @OriginalSize * @Factor
-- Attempt to shrink down the log file
PRINT 'Minimum Quantity : '+CAST( @MaxCount AS VARCHAR(10) )
PRINT 'Maximum Time : '+CAST( @MaxMinutes AS VARCHAR(10) )+' minutes ('+CAST( @MaxMinutes*60 AS VARCHAR(10) )+' seconds)'
PRINT ''
SET @Counter = 0;
SET @StartTime = GETDATE();
--loop the padding code to reduce the log while
-- within time limit and
-- log has not been shrunk enough
WHILE (
(@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND
(@OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) AND
((@OriginalSize * 8 / 1024) > @NewSize)
)
BEGIN --Outer loop.
--pad out the logfile a page at a time while
-- number of pages padded does not exceed our maximum page padding limit
-- within time limit and
-- log has not been shrunk enough
WHILE (
(@Counter < @MaxCount) AND
(@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND
(@OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) AND
((@OriginalSize * 8 / 1024) > @NewSize)
)
BEGIN --Inner loop
INSERT INTO DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE FROM DummyTrans
SELECT @Counter = @Counter + 1
--Every 1,000 cycles tell the user what is going on
IF ROUND( @Counter , -3 ) = @Counter
BEGIN
PRINT 'Padded '+LTRIM( CAST( @Counter*8 AS VARCHAR(10) ) )+'K @ '+LTRIM( CAST( DATEDIFF( ss, @StartTime, GETDATE() ) AS VARCHAR(10) ) )+' seconds';
END
END
--See if a trunc of the log shrinks it.
EXEC( @TruncLog )
END
PRINT ''
SELECT @StringData = '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;
PRINT @StringData
PRINT ''
DROP TABLE DummyTrans;
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
---**********************************---
Hope this helps.
-
Its working. Thanks a lot David.
But I can't understand the functinality of this Script, can u pls explain me.
Sathish
S A T ...
Sathish's Blog -
Sure.
Transaction logs basically wrap around. If the log has space, but that space is not at the end, but in the middle, then it cannot be shrunk with the conventional dbcc shrink command.
What this script does is 'fill up' the blanks, so that when it truncates, it can be shrank using the shrink portionof the script.
Hope this makes sense.
Cheers.
-
Hi David,
What can you say about this article?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
It says...
"When you execute a DBCC SHRINKFILE statement SQL Server 2000 fills the remaining part of virtual log with dummy records"
-
hi Sheckster,
Nice one.
from
Killer
-
yup, but what about the blanks in between...?
-
Hi,
As per BOOKS ON LINE . The Artical truncating the log file gives us information that all the space is freed except the Active portion of the transaction log when we isssue the DBCC SHRINKFILE(,).
backup log command with truncate_only shrink the log file size on disk.
So free space and blanks are removed except the active portion when we issue DBCC SHRINKFILE(,) command.
From
Killer
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply