reducir tamaño del LOG

[orodriguezc]

Hola foristas,


Mi problema es el siguiente: tengo una base de datos SQL SERVER 7.0 con una gran cantidad de información, 37 GB repartido en 3 device (solo datos, sin el log). Hasta aqui todo es correcto, pero ocasionalmente el log de transacciones crece de manera exagerada y alcanza a tener dimensiones alarmantes (cerca de 40 GB), pero los device de datos permanece practicamente iguales. Como puedo determinar el por qué el Log de Transacciones crece tanto?, Como puedo reducir nuevamente el tamaño del log de transacciones?.

En las Propiedades de la Base de Datos tengo activada la opción de "Truncar registro en punto de comprobación".

Agradezco cualquier comentario o sugerencia.

[smarcet]

Yo utilizo un metodo de guardar cada hora el registros de transacciones, esto produce el vaciado del registro de transacciones y que no continúe aumentando, pero lo que no evita es que una vez ampliado el registro, este continúe así.

Me gustaría que me comentaras como activas la opcion de auto truncamiento.

[orodriguezc]

Empezado por smarcet

Me gustaría que me comentaras como activas la opcion de auto truncamiento.

La forma que conozco para activar la opción de autotruncamiento es la siguiente:

En el "SQL Server Enterprise Manager" seleccionas tu base de datos. Haces click derecho y del menu contextual seleccionas "Propiedades". Esto te muestra las propiedades de tu Base de datos. Aqui seleccionas la página Opciones y activas la casilla que dice "Truncar registro en punto de Comprobación".

Se que también se puede establecer esta opción mediante Stored procedure, pero nunca lo he hecho.


Por otra parte pido excusas a los miembros del club porque pude resolver mi problema y olvide comentarles como lo solucioné . Bueno después de varias horas de estar navegando por muchisimos sitios en la web encontré el siguiente código (disculpen los moderadores pero olvide el link de la pagina):

SET NOCOUNT ON
DECLARE @NombreLogicoArchivo sysname,
        @MaxMinutos INT,
        @NuevoTamano INT

/* Indique aquí su configuración. */
-- Nombre de la base de datos para el que se va a truncar el registro
USE  baandb 

-- Use sp_helpfile para identificar el nombre de archivo lógico 
-- que desea reducir.
set  @NombreLogicoArchivo = 'baandblog' 

-- Límite de tiempo permitido para dar la vuelta al registro. 
set  @MaxMinutos = 10            
set  @NuevoTamano = 500   -- en MB

-- Configuración e inicio 
DECLARE @TamanoOriginal int

SELECT @TamanoOriginal = size -- en páginas de 8 KB 
  FROM sysfiles
  WHERE name = @NombreLogicoArchivo;
 
SET ' El tamaño original del registro de ' + 
      db_name() +  ' es ' + 
     CONVERT(VARCHAR(30),@TamanoOriginal) + 
     ' páginas de 8 KB ó ' + 
     CONVERT(VARCHAR(30),(@TamanoOriginal *8/1024)) + 'MB'
  FROM sysfiles
  WHERE name = @NombreLogicoArchivo;
 

CREATE TABLE DummyTrans(DummyColumna char (8000) not null)


-- Dar la vuelta al registro y truncarlo. 
DECLARE @Contador   INT;
DECLARE @HoraInicio DATETIME;
DECLARE @TruncReg  VARCHAR(255);

SET  @HoraInicio = GETDATE();
SET  @TruncReg = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

-- Intentar una reducción inicial. 
DBCC SHRINKFILE (@NombreLogicoArchivo, @NuevoTamano)
EXEC (@TruncReg)

-- Dar la vuelta al registro, si es necesario.
-- no se ha excedido el máximo tiempo establecido 
WHILE     @MaxMinutos > DATEDIFF (mi, @HoraInicio, GETDATE())
     -- no se ha reducido el registro 
      AND @TamanoOriginal = (SELECT size FROM sysfiles 
                                          WHERE name =  @NombreLogicoArchivo)  

     -- El valor pasado para el tamaño nuevo es más pequeño que el tamaño actual. 
      AND (@TamanoOriginal * 8 /1024) > @NuevoTamano  
  BEGIN -- Bucle externo. 
    SELECT @Contador = 0 WHILE  ((@Contador < @TamanoOriginal / 16) AND (@Contador < 50000))
    
  BEGIN -- Actualización 
-- Como es un campo de tipo char, inserta 8000 bytes.
     
   INSERT DummyTrans VALUES ('Llenar registro')  
        DELETE DummyTrans
        SELECT @Contador = @Contador + 1
      END   -- Actualización Probar si un truncamiento reduce de tamaño el registro. 
    EXEC (@TruncReg)  
  END   -- Bucle externo
 
SELECT ' El tamaño final del registro de ' + 
           db_name() + ' es de ' + CONVERT(VARCHAR(30),size) + 
           ' páginas de 8 KB ó ' + CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
  FROM sysfiles 
  WHERE name = @NombreLogicoArchivo

DROP TABLE DummyTrans
PRINT '*** RECUERDE: DEBE REALIZAR UNA COPIA DE SEGURIDAD COMPLETA DE LA BASE DE DATOS***'
SET NOCOUNT OFF

Es posible que se requiera ejecutar este scripts varias veces hasta obtener el tamaño del LOG deseado. Espero le sirva a alguien.

Hasta luego.

[guillotmarc]

Hola.

Este Script estaba hace un par de años en la MSDN OnLine de Microsoft, pero lo busqué para pasarte el enlace y no he sido capaz de encontrarlo.

Venía con una buena descripción del problema, (algo que ver con el formato circular del registro de transacciones, por lo que no puede asegurar que el espacio liberado esté siempre al final, y se pueda acortar el registro).

NOTA : Teoricamente el truncamiento del registro se produce cuando se realizan copias de seguridad completas, o bien mediante las instrucciones DBCC SHRINKFILE y DBCC SHRINKDATABASE (Consultar documentación). Pero realmente lo único que me funcionó a mi, es el código que has publicado. (Este crecimiento del registro, y la dificultad de instalar automáticamente SQL Server junto con la aplicación fueron las principales razones por las que abandoné SQL Server y me pasé a Interbase/Firebird).

Saludos.