SQL Server - Reducir archivo Log o base de datos en sql server

 
Vista:

Reducir archivo Log o base de datos en sql server

Publicado por Ivonne (54 intervenciones) el 21/07/2008 18:55:39
Hola buenos días, quisiera enterder bien el concepto de Reducción manual de una base de datos o de un archivo de base de datos.Tengo una base de datos con las siguientes caracteristicas:

dbasa_data el total es de 4407.5 Mb Disponible:129.94 Mb y Utilizado:4277.56Mb

dbasa_log el total es de 71.12Mb Disponible:63.2Mb yutilizado:7.92Mb

Tengo dudas de como saber con base a esos datos ¿Cuando es momento de realizar una reduccion por archivo o la base de datos?

e igual del porcentaje que le establezco para realizar la reduccion ¿A que parte de la base de datos afecta y si no puede ocasionar que se pierdan los datos?

¿Este procedimiento si lo hago cuando los usuarios estan trabajando en el sistema puedo ocasionar errores en la base de datos o es transparente como es el caso de los planes de mantenimiento?

Por favor agradeceré su apoyo ya que soy relativamente nueva en realizar esta tarea para el buen funcionamiento de la bd de la empresa....saludos a todos y gracias de antemano.
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

RE:Reducir archivo Log o base de datos en sql ser

Publicado por Isaias (3308 intervenciones) el 22/07/2008 00:39:45
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
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

RE:Reducir archivo Log o base de datos en sql ser

Publicado por Ivonne (54 intervenciones) el 22/07/2008 01:03:23
Hola Isaias,gracias por atender mi mensaje, pero si me gustaria que me explicarás que hace cada store procedure, pues como dije no habia realizado esta tarea pero por el crecimiento de mi base de datos considero que ya es necesario usarlo, como decia en mi mensaje con base a los datos que hoy en día tengo en mi base de datos lo envie en mi primer mensaje como saber que es necesario realizar esa tarea e igual tengo duda si puedo realizar esa tarea aun cuando los usuarios esten trabajando con el sistema o se tienen que salir todos para realizar la reduccion...saludos.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

RE:Reducir archivo Log o base de datos en sql ser

Publicado por Isaias (3308 intervenciones) el 22/07/2008 03:30:27
En resumen, lo que hacen ambos stores, es un analisis de tus BASES y determinan de acuerdo al porcentaje de OCUPACION VS USO, si se debe realizar el vaciado del log (TRUNCATE ONLY) y la reduccion de la base (SHRINFILE).

Eso es lo que hacen, por eso te pedi que los analizaras.

Saludos.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

RE:Reducir archivo Log o base de datos en sql ser

Publicado por Ivonne (54 intervenciones) el 22/07/2008 05:43:40
Ok,gracias..lo reviso y cualquier duda te molesto de nuevo...saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar