RE:Reducir archivo Log o base de datos en sql ser
Hola Ivonne
Tal vez estos 2 stores te ayuden en tu cometido.
CREATE PROCEDURE sp_force_shrink_log
@target_percent tinyint = 0,
@target_size_MB int =10,
@max_iterations int = 1000,
@backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on
declare @db sysname,
@last_row int,
@log_size decimal(15,2),
@unused1 decimal(15,2),
@unused decimal(15,2),
@shrinkable decimal(15,2),
@iteration int,
@file_max int,
@file int,
@fileid varchar(5)
select @db = db_name(),
@iteration = 0
create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateTime varchar(50)
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo,
StartOffset )
create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles
where status & 0x40 = 0x40
select @file_max = @@rowcount
if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity,
CreateTime ) exec ( 'dbcc loginfo' )
select @last_row = @@rowcount
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
select @unused1 = @unused
select 'iteration' = @iteration,
'log size, MB' = @log_size,
'unused log, MB' = @unused,
'shrinkable log, MB' = @shrinkable,
'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 /
@log_size )
while @shrinkable * 100 / @log_size > @target_percent
and @shrinkable > @target_size_MB
and @iteration < @max_iterations begin
select @iteration = @iteration + 1 -- this is just a precaution
exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')
select @file = 0
while @file < @file_max begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( 'dbcc shrinkfile( ' + @fileid + ' )' )
end
exec( 'backup log [' + @db + '] ' + @backup_log_opt )
truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status,
Parity, CreateTime ) exec ( 'dbcc loginfo' )
select @last_row = @@rowcount
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
select 'iteration' = @iteration,
'log size, MB' = @log_size,
'unused log, MB' = @unused,
'shrinkable log, MB' = @shrinkable,
'shrinkable %' = convert( decimal(6,2), @shrinkable * 100
/ @log_size )
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'
exec( 'drop table table_to_force_shrink_log' )
GO
CREATE PROCEDURE sp_force_shrink_log_min
AS
SET NOCOUNT ON
DECLARE @MaxMinutes INT, @NewSize INT, @Factor FLOAT;
SET @NewSize = 1
SET @MaxMinutes = 1
SET @Factor = 0.95
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
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('[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.
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')
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
GO