Re: How do I reduce the physical size of the file?
From: evan b (evan_at_cheapaschips.com.au_removethis)
Date: 12/20/04
- Next message: Tom Swanick: "Configuring users"
- Previous message: jouj: "How do I reduce the physical size of the file?"
- In reply to: jouj: "How do I reduce the physical size of the file?"
- Next in thread: Tibor Karaszi: "Re: How do I reduce the physical size of the file?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 20 Dec 2004 20:20:58 +1030
This isnt mind but I use it often
it works great I frequently reduce 6gb logs
--------------------
/*
Shrink a named transaction log file belonging to a database
*/
SET NOCOUNT ON;
DECLARE @MaxMinutes INT, @NewSize INT, @Factor FLOAT;
/*
The process has several control parameters, most of the time you only need
to
worry about the first few as these are the big controls whereas the fifth
is
simply a fine tuning control.
Switch to the database we are attempting to shrink the logs for. Uncomment
this to
automatically switch to a database, otherwise it uses the current db.
*/ --USE [MyDatabase];
-- Define the ideal size of logfile in MB and also how much time may be used
to shrink the log SET @NewSize = 1; SET @MaxMinutes = 1;
SET @NewSize = 100; SET @MaxMinutes = 10;
/*
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 = 0.95;
/*
Automatically retrieve the logical filename of the largest logfile
currently
assigned to the database. If you want to manually select the file to
shrink
then you can retrieve a list of the files using sp_helpfile and then
setting
@LogicalFileName manually.
*/
DECLARE @LogicalFileName SYSNAME,
@MaxSize INT,
@OriginalSize INT,
@StringData VARCHAR(500);
SELECT TOP 1
@LogicalFileName = RTRIM( [name] ),
@MaxSize = CASE [maxsize]
WHEN -1 THEN -1
ELSE ([maxsize] * 8)/1024
END
FROM sysfiles
WHERE [groupid] = 0
ORDER BY [size] DESC;
/*
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. */
--Check user size is not larger than maximum file size
IF @MaxSize > 0 AND @NewSize > @MaxSize
BEGIN
PRINT 'Note: '+CAST( @NewSize AS VARCHAR )+'MB exceeds the limit of
'+CAST( @MaxSize AS VARCHAR )+'MB on this file, correcting...'
PRINT ''
SET @NewSize = @MaxSize;
END
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. (this is what causes data to be returned) 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) ); IF @MaxMinutes
= 1
PRINT 'Maximum Time : '+CAST( @MaxMinutes AS VARCHAR(10) )+' minute
('+CAST( @MaxMinutes*60 AS VARCHAR(10) )+' seconds)'; ELSE
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
SET @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
/*
Based on:
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 w
as 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
17.01.2003
Modified timing display code to handle 1 minute correctly
08.05.2003
Modified the instructions to make them easier to read & understand
*/
------------------------------
"jouj" <jouj@discussions.microsoft.com> wrote in message
news:233BAB50-5AE8-41A3-B5A2-002FABA25790@microsoft.com...
> Hi,
> I have an MS SQL Server 2000 Database with a huge physical log file
(14Gb).
>
> I tried:
> 1-Backup Log <MyDB>
> 2-DBCC SHRINKDATABASE (<MyDB>,10)
> 3-Backup Log <MyDB>.(again)
> The file has not decreased.(from the 14Gb I have 13.3Gb Available Space)
> MyDb file size = 305 Mb.
>
> How do I reduce the physical size of the file?
>
> Any Help is very Appreciated
> Thanks in Advance
> G.H.
>
- Next message: Tom Swanick: "Configuring users"
- Previous message: jouj: "How do I reduce the physical size of the file?"
- In reply to: jouj: "How do I reduce the physical size of the file?"
- Next in thread: Tibor Karaszi: "Re: How do I reduce the physical size of the file?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
