Reducir tamaño del blog de Transacciones en BD
Basado en el artículo: http://www.helpdna.net/sqlserver_faq_01_reducir_log_transacciones.htm
Existen varias razones que pueden hacer que el registro de transacciones no se reduzca. El registro de transacciones es “circular”. Esto quiere decir que en cualquier momento puede haber archivos VLF con espacio “libre” o “reutilizable” al comienzo, en medio, o al final del registro. Para reducir el registro debe haber espacio “libre” al final del registro, no sólo espacio libre en cualquier otra parte del registro. Además, sólo se pueden reducir archivos VLF enteros.
Causas habituales del crecimiento del Log de Transacciones ( Transaction Log ).
Si el log ha crecido mucho es porque SQL Server lo ha necesitado. Esto es debido a una de las siguientes causas:
-
Eso es lo que normalmente sucede y se debería ajustar la estrategia de backup para hacer copias del log más a menudo.
-
Si el crecimiento del log se debe a una ejecución (insert, update, delete) que afecta a un gran número de registros, bien por haber lanzado un proceso de actualización masiva o porque alguien ha ejecutado una consulta mal formada, que habría que detectarla (y darle un tirón de orejas al que la haya enviado).
Las copias completas de la base de datos no truncan el registro de transacciones. Utiliza una estrategia de copia de seguridad que mezcle copias completas de la base de datos con copias del registro de transacciones.
Problemas habituales que impiden reducir el tamaño del Log de Transacciones ( Transaction Log ).
Los pasos para truncar el Transaction log pueden no ser tan obvios como pueda parecer:
El registro de transacciones está compuesto por al menos dos registros virtuales (VLF = Virtual Log Files). El truncado del registro de transacciones se realiza VLF a VLF. Si sólo tienes dos registros virtuales y te ocupan todo el fichero no podrás truncarlo, aunque dudo que cada VLF llegue a ocupar mucho espacio. (Para ampliar información sobre este punto, consulta en la ayuda ‘Trucar el Registro de transacciones’, encontrarás información detallada y un gráfico muy explicativo)
Al ejecutar una instrucción DBCC SHRINKFILE solo se le indica a SQL Server que se quiere reducir el tamaño físico del fichero de LOG. Si el último VLF está al final del log, aunque el resto del fichero esté vacío, no se podrá truncar el fichero, ya que SQL Server sólo puede reducirlo recortando por el final.
Supongamos que hay una estrategia de copia de seguridad que incluye copias completas y copias del log. En este caso son las copias del log las únicas que truncan el registro de transacciones, por lo que si se ha ejecutado o no DBCC SHRINKFILE, el registro no se truncará lógicamente hasta que se haga una copia de seguridad del log (o se ejecute BACKUP LOG TuBase WITH TRUNCATE_ONLY).
Sin embargo si el último VLF no está completo, no se podrá truncar, por lo que se tendrá que forzar su llenado. Al ejecutar DBCC LOGINFO(TuBase) se obtendrá una lista de VLF, si te fijas en la columna Status, 2 significa que no está activo o que al menos no es reutilizable. Envía alguna actualizaciones nulas (UPDATE TuTabla SET Campo1 = Campo1, por ejemplo) y vuelve a ejecutar el comando DBCC LOGINFO hasta que veas que hay algún otro VLF con status 2.
Ahora si que se puede ejecutar el BACKUP LOG para truncar el LOG y tras esto SQL
Server podrá recortar el fichero físicamente eliminando uno o más VLFs.
Basado en el artículo: http://www.programacion.com/articulo/reducir_el_fichero_de_log_en_sql_server_269
Si no nos interesa tener copia de seguridad de todos los datos del fichero de log (o no hay espacio para la copia de seguridad) podemos seguir un método más rápido pero que no hace copia de seguridad de este fichero, aunque sí del de la base de datos:
-
USE MiBase
-
CHECKPOINT
-
EXEC sp_addumpdevice ‘disk’, ‘CopiaMiBase’, ‘d:LogMiBase.bak’
-
BACKUP DATABASE MiBase TO CopiaMiBase
-
BACKUP LOG MiBase WITH TRUNCATE_ONLY
-
Para que no vuelva a ocurrir hay que tener un plan de mantenimiento de la base de daos que realice copias de seguridad completas y del archivo de log cada cierto tiempo. Cuánto tiempo es difícil de decir sin saber para que se utiliza la base de datos y cual es su tamaño, pero podría ser desde varias veces al día hasta una vez por semana.
Desde luego es mucho más sencillo si programamos como trabajo la realización de las copias de seguridad y si programamos alguna alerta que nos indique si se sobrepasa el límite que consideremos razonable para el tamaño de nuestros ficheros. Siempre es mejor prevenir los errores que corregirlos.