prevelik log fajl

[ cTaHk0 @ 07.07.2006. 13:22 ] @

A evo i finog skripta koji ce log file shrinkovati na optimalnu velicinu...

/**************************************************************
Input Params:
-------------
@target_percent tinyint. default = 0. Target percentage of remaining shrinkable
space. Defaults to max possible.
@target_size_MB int. default = 10. Target size of final log in MB.
@max_iterations int. default = 1000. Number of loops (max) to run proc through.
@backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup options.
*************************************************************/

-- DB parameters
DECLARE @target_percent tinyint
DECLARE @target_size_MB int
DECLARE @max_iterations int
DECLARE @backup_log_opt nvarchar(1000)

SET @target_percent = 0
SET @target_size_MB = 0
SET @max_iterations = 1000
SET @backup_log_opt = 'WITH TRUNCATE_ONLY'

DECLARE @db SYSNAME
DECLARE @last_row INT
DECLARE @log_size DECIMAL(15,2)
DECLARE @unused1 DECIMAL(15,2)
DECLARE @unused DECIMAL(15,2)
DECLARE @shrinkable DECIMAL(15,2)
DECLARE @iteration INT
DECLARE @file_max INT
DECLARE @file INT
DECLARE @fileid VARCHAR(5)
DECLARE @prev_max_iterations INT
DECLARE @command VARCHAR(500)

SET NOCOUNT ON

SET @db = db_name()
SET @iteration = 0
SET @prev_max_iterations = 2^31-1

IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo

-- This table stores the results of DBCC LOGINFO
CREATE TABLE #loginfo(
id INT identity,
FileId INT,
FileSize NUMERIC(22,0),
StartOffset NUMERIC(22,0),
FSeqNo INT,
Status INT,
Parity SMALLINT,
TimeorLSN VARCHAR(25))

CREATE UNIQUE CLUSTERED INDEX loginfo_FSeqNo ON #loginfo ( FSeqNo, StartOffset )

IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles

CREATE TABLE #logfiles (
id INT IDENTITY(1,1),
fileid VARCHAR(5) NOT NULL)

INSERT INTO #logfiles ( fileid )
SELECT CONVERT(VARCHAR, fileid )
FROM sysfiles
WHERE status & 0x40 = 0x40

SET @file_max = @@ROWCOUNT

INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'dbcc loginfo' )

SET @last_row = @@rowcount

PRINT 'Max iterations = ' + rtrim(cast(@max_iterations as varchar(20)))

SELECT @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
FROM #loginfo

SET @unused1 = @unused -- save for later

-- changed this so that it will print with rest of output SBP
PRINT '
iteration ........... = ' + cast(@iteration as varchar(10)) + '
log size, MB ........ = ' + cast(@log_size as varchar(10)) + '
unused log, MB ...... = ' + cast(@unused as varchar(10)) + '
shrinkable log, MB .. = ' + cast(@shrinkable as varchar(10)) + '
shrinkable % ........ = ' + cast(convert( DECIMAL(6,2), @shrinkable * 100 / @log_size ) as varchar(10))

-- If @target_size_MB is entered, it will take precedence
-- over @target_percent, by calculating a new @target_percent.
IF @target_size_MB > 0
SET @target_percent = (@target_size_MB / @log_size) * 100
ELSE
SET @target_size_MB = 10

-- Changed @target_percent to + 1, because many times the end result is
-- slightly larger than the target.
WHILE @shrinkable * 100 / @log_size > (@target_percent + 1)
AND @iteration < @max_iterations
BEGIN
SET @iteration = @iteration + 1 -- this is just a precaution

SET @file = 0

WHILE @file < @file_max
BEGIN
SET @file = @file + 1

SELECT @fileid = fileid
FROM #logfiles
WHERE [id] = @file

SET @command = 'DBCC SHRINKFILE( ' + @fileid + ',' + RTRIM(CAST(@target_size_MB as varchar(10))) + ')'

PRINT @command
EXEC (@command)
END

EXEC( 'BACKUP LOG [' + @db + '] ' + @backup_log_opt )

TRUNCATE TABLE #loginfo
INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'DBCC LOGINFO' )

SET @last_row = @@ROWCOUNT

-- The most iterations we really need to do is the number of logical log files,
-- and this should change if the log is shrinking. Therefore, reset
-- @max_iterations within the loop.
SELECT @max_iterations = COUNT(*)
FROM #loginfo

-- If the number of logical log files did not change from last iteration, get out.
IF @max_iterations = @prev_max_iterations
SET @max_iterations = 0
ELSE
SET @prev_max_iterations = @max_iterations

PRINT 'Max iterations = ' + RTRIM(CAST(@max_iterations as varchar(20)))

SELECT @log_size = SUM( FileSize ) / 1048576.00,
@unused = SUM( CASE WHEN Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00,
@shrinkable = SUM( CASE WHEN id < @last_row - 1 and Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00
FROM #loginfo

PRINT '
iteration ........... = ' + CAST(@iteration AS VARCHAR(10)) + '
log size, MB ........ = ' + CAST(@log_size AS VARCHAR(10)) + '
unused log, MB ...... = ' + CAST(@unused AS VARCHAR(10)) + '
shrinkable log, MB .. = ' + CAST(@shrinkable AS VARCHAR(10)) + '
shrinkable % ........ = ' + CAST(CONVERT( DECIMAL(6,2), @shrinkable * 100 / @log_size ) AS VARCHAR(10))
END

IF @unused1 < @unused
SELECT 'After ' + CONVERT( VARCHAR, @iteration ) +
' iterations the unused portion of the log has grown from ' +
CONVERT( VARCHAR, @unused1 ) + ' MB to ' +
CONVERT( VARCHAR, @unused ) + ' MB.'
UNION ALL
SELECT 'Since the remaining unused portion is larger than 10 MB,' WHERE @unused > 10
UNION ALL
SELECT 'you may try running this procedure again with a higher number of iterations.' WHERE @unused > 10
UNION ALL
SELECT 'Sometimes the log would not shrink to a size smaller than several Megabytes.' WHERE @unused <= 10

ELSE
SELECT 'It took ' + convert( VARCHAR, @iteration ) +
' iterations to shrink the unused portion of the log from ' +
CONVERT( VARCHAR, @unused1 ) + ' MB to ' +
CONVERT( VARCHAR, @unused ) + ' MB'

-- cleanup
IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo

IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles
GO