[SQL-LOG] – Reducir el LOG de una Base de Datos.


Saludos amigos después de un tiempo de no postear nada, les dejo un script que reduce el log de una base de datos.

Supongamos que el siguiente archivo LOG pesa demasiado y lo queremos reducir.

image

Como primer paso y previniendo cualquier error que se pueda generar durante la ejecución de la reducción del LOG tendremos que hacer un respaldo de la BD.

 

USE master
GO

BACKUP DATABASE prueba
TO DISK = N'C:\RespaldosBD\Prueba_Full.BAK'
GO

BEGIN TRY

    DECLARE @backupSetId AS INT
    
    SELECT @backupSetId = position
    FROM msdb..backupset
    WHERE database_name=N'Prueba'
       AND backup_set_id=(SELECT MAX(backup_set_id)
                          FROM msdb..backupset
                          WHERE database_name=N'Prueba' )

    IF @backupSetId IS NULL
    BEGIN
       RAISERROR(N'Fallo la verificación. La información de reapaldo para la base de datos, no se encontro.', 16, 1)
    END    
    
    RESTORE VERIFYONLY
    FROM  DISK = N'C:\RespaldosBD\Prueba_Full.BAK'
    WITH  FILE = @backupSetId    

    PRINT 'La verificación se realizo correctamente, el respaldo de la base de datos esta listo para utilizarse.'

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
    PRINT ERROR_LINE()
    PRINT ERROR_NUMBER()
END CATCH
GO

Si todo salió bien tendremos nuestro respaldo en la ubicación indicada en el script anterior.

image

 

Ahora, respaldamos el archivo de LOG y le aplicamos un SHRINKFILE.

El SHRINKFILE Reduce el tamaño del archivo de datos o de registro para la base de datos actual, o vacía un archivo moviendo los datos del archivo especificado a otros archivos del mismo grupo de archivos, permitiendo quitar el archivo de la base de datos. Puede reducir un archivo a un tamaño menor que el tamaño especificado cuando se creó. Así se restablece el tamaño mínimo de archivo al valor nuevo.

@http://msdn.microsoft.com/es-es/library/ms189493.aspx

El CHECKPOINT Escribe en disco todas las páginas desfasadas de la base de datos actual. Las páginas desfasadas son páginas de datos que se han incluido en el caché la memoria y se han modificado, pero todavía no se han guardado en el disco. Los puntos de comprobación permiten ahorrar tiempo en una recuperación posterior al crear un punto en el que se garantiza que todas las páginas desfasadas se hayan escrito en el disco.

El script final:

USE Prueba
CHECKPOINT
BACKUP LOG Prueba TO DISK = 'C:\RespaldosBD\Prueba_Log.trn'
DBCC SHRINKFILE (N'Prueba_Log' , 0, TRUNCATEONLY)
GO

Si todo se ejecuto correctamente tendremos el archivo LOG reducido ‘”Truncado” (para este caso).

Resultado:

image

image 

image

Me hubiera gustado hacer el ejemplo con LOG más grande, pero al momento solo cuento con este, aun así debe funcionar de la misma manera.

Nota: Me paso que al ejecutar el SHRINKFILE a una base de datos desde un acceso remoto, me decía que el archivo LOG no existía aunque físicamente ahí estuviera, después de probar un rato, logre que funcionara, en lugar de utilizar el nombre del archivo LOG, utilice el file_id y listo todo se resolvió.

¿Como saber el File_id?

Si ejecutamos el siguiente query obtendremos los siguientes resultados.

USE Prueba
SELECT * FROM SYS.DATABASE_FILES

image

Ahora que ya sabes el file_id lo usamos en el SHRINKFILE:

USE Prueba
CHECKPOINT
BACKUP LOG Prueba TO DISK = 'C:\RespaldosBD\Prueba_Log.trn'
DBCC SHRINKFILE (2 , 0, TRUNCATEONLY)
GO

Listo, con eso se debe reducir el archivo LOG de una BD.

Espero les sea de ayuda, espero comentarios!!!.

About these ads
    • Edgar Delgado Salas
    • 6/10/10

    En verdad tu ayuda es la mas explicita que encontre, en servidores sql2008 la segunda opcion es la funciona sacando el “file_id”.

    Gracias por tu ayuda

    • IbarrGlez
    • 23/01/11

    Gracias me fue de mucha ayuda, estuve buscando por internet por mucho tiempo y ningun procedimiento me funcionaba.
    de nuevo Gracias y estoy a sus ordenes…..

    • IbarrGlez
    • 23/01/11

    Te agradezco por tu ayuda, me fue de mucha utilidad. Gracias.

    • marcelo
    • 18/03/11

    excelente post¡¡¡¡¡ muy util. me quito un peso de encima

    • Cuau Hern
    • 15/04/11

    Que buen post, muy bien explicado y de los mejores, gracias por la info

    • Julieth
    • 1/06/11

    Excelente explicación…mejor no pudiste explicarlo y me sirvio a la perfección. muchisisisisimas gracias!!!! :)

  1. Me ayudo mucho esta explicación..

    • Manuel Perez
    • 22/08/11

    Excelente!!! yo logré reducir el LOG en una base de datos de tamaño 175Gb y la Bd solo de 1.75Gb…. El Log al finas quedo de 507kb lo maximo!!! luego configure el SQL server para queel crecimiento del Log no sea tan grande!!!

    gracias por tu aporte!!!!

    • marlom
    • 23/11/11

    Me funciono de maravilla, solo que me quede con la duda de lo que comenta Manuel Perez, al decir que configuraste sql par hebitar que el log cresca es con mode de recuperacion Registro Masivo.

    • Carlosrpo
    • 15/12/11

    Solo un pregunta, para realizar este proceso es necesario que la base de datos no este siendo accedida, estoy en la correcto?

    • psykrest
    • 15/12/11

    De preferencia realizalo cuando no tengas demasiada carga de trabajo la BD, ya que se pueden presentar problemas de performance, debes de tener cuidado con la fragmentación de los indices, tambien dependiendo de la BD el Shrink puede tomar mas tiempo de lo que uno espera ya que trabaja moviendo las paginas de datos del archivo de tu BD para consildar el espacio libre. Saludos Ivan

    • Leo
    • 13/09/12

    Me sale que “Could not locate file” es decir el log……..

  2. Leo me sucedio lo mismo y para ello tuve que ejecutar nuevamente la consulta de Generacion de Backup de BD que está al inicio, y luego ejecutar
    USE Prueba
    CHECKPOINT
    BACKUP LOG Prueba TO DISK = ‘C:\RespaldosBD\Prueba_Log.trn’
    DBCC SHRINKFILE (2 , 0, TRUNCATEONLY)
    GO

    recuerda que la sentencia 2 es usando el File_ID.

    Saludos!!

    • Ing. Juan Mancilla
    • 21/10/12
    • Camel
    • 18/06/13

    muy bueno

  1. No trackbacks yet.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

%d personas les gusta esto: