Te presento mi nuevo sitio web: "El Futuro de los Datos"

Aunque SQL Server Si!, seguirá activo, iré bajando la frecuencia de publicación.
Si quieres conocer todas las novedades que vaya publicando, te recomiendo que lo visites y te suscribas. Tengo un regalito para mi audiencia:

Tu primer Dashboard en "piloto automático" listo en 30 minutos
Sólo para suscriptores.
.

22 dic. 2008

Cómo Reducir Log de Transacciones de SQL Server

Soluciones rápidas al problema.

Hacer Backup del Log de Transacciones ( Transaction Log ) y reducir el fichero.

  1. Ejecuta dos o tres veces la instrucción CHECKPOINT. Esto asegurará que todas las páginas de memoria se han escrito en el fichero de datos.
  2. Luego haz un BACKUP LOG WITH TRUNCATE_ONLY para que trunque el registro de transacciones.
  3. Posteriormente ejecutas DBCC SHRINKFILE indicando el nombre del fichero del log a reducir.

    (En la ayuda puedes ampliar información sobre estos dos mandatos).

Eliminar el fichero para que se genere de Nuevo (Esta solución es demasiado drástica, emplearla solo si falla la anterior):

  1. Pon la base de datos en modo "single user".
  2. Ejecuta CHECKPOINT dos o tres veces. Esto asegurará que todas las páginas de memoria se han escrito en el fichero de datos.
  3. Asegúrate de que no hay conexiones abiertas a la base de datos, con lo que no puede haber transacciones a medio ejecutar.
  4. Utiliza sp_detach_db para desconectar dicha base de datos.
  5. Elimina el fichero de log.
  6. Utiliza sp_attach_db para reconectar la base de datos. SQL Server creará un nuevo fichero de log.

    ¡¡¡ IMPORTANTE !!!
    Si no ejecutas el proceso completamente y en este orden, podrías tener problemas de consistencia de información en el fichero de datos.
    Por ejemplo, si apagas el equipo sin más, SQL Server no ha tenido tiempo de volcar las páginas de datos de la memoria al disco. Al reiniciar SQL Server, el problema será corregido utilizando la información contenida en el registro de transacciones, pero si este no está presente, el archivo de datos se dará por bueno, y podría ser realmente inconsistente.

    Otro detalle importante a tener en cuenta es que el log no se limpia nunca completamente, ya que siempre hay operaciones internas que SQL Server necesita mantener en él.

 

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.

Puedes detectar las consultas enviadas a SQL Server con el Profiler.

No debes borrar el registro de transacciones manualmente salvo causa de fuerza mayor. Lo que debes hacer es diseñar una estrategia de copia de seguridad que sea acorde con el volumen de transacciones que tiene tu sistema.

 

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.

 

Enlaces con información de Microsoft sobre el tema.

INF: Cómo reducir el registro de transacciones de SQL Server

A consultar en los Books On Line y ampliar información:

DBCC SHRINKFILE
DBCC SHRINKDATABASE
DBCC LOGINFO
BACKUP LOG
sp_attach_db
sp_detach_db

7 comentarios:

Rafa dijo...

Un apunte que podría resaltar toda mi ignorancia sobre el registro de transacciones: Antes de aplicar las soluciones rápidas (muy bien expuestas), asegurarse de que no hay publicaciones de réplicas de la Base de Datos.

Salvador Ramos dijo...

Hola Rafa,
Sólo añadir que afecta solamente a las replicaciones transaccionales. Y que también afecta, por ejemplo, a log shipping.

En este artículo se comenta cómo realizar la reducción del log, pero hay que tener en cuenta las consecuencias de dejar de disponer de esa información si haber hecho antes un backup de ella.

Un saludo
Salvador Ramos

RSOLANO dijo...

Buenos días, primero que todo te felicito por el blog me ha ayudado mucho.
quería pedirte el favor que me enviaras o publicaras como seria el script para eliminar el log ya que no he podido.
Muchas gracias.

Lic. Enel R. Almonte dijo...

No puedo darle un tiron de orejas a mi jefe que se invento una supercosulta que hace que log cresca hasta 50GB en 2 meses

Salvador Ramos dijo...

Hola Enel,

N puedes, o al menos no debes :)
Pero si que puedes implantar una política de backup adecuada que evite ese crecimiento.

Un saludo
Salvador Ramos

Gabriiella dijo...

Hola, tengo un problema, soy novata en esto... resulta que tengo dos ambientes, uno que es el productivo y otro que es QA, en productivo esta sucediendo el problema "El registro de transacciones de la base de datos '' está lleno.", este problema lo quieren replicar a QA, cómo puedo hacer una copia de productivo y replicarlo en QA, contando con que mi usuario de productivo solo tiene permisos de consulta. Espero puedas ayudarme.

salvador ramos dijo...

Hola Gabriela,

En tu base de datos de QA puedes crear un transaction log al que limites el crecimiento, ejecutar transacciones hasta llenarlo. Pero no veo que te aporte nada reproducir ese problema.

Lo que debes hacer es poner tamaños adecuados del transaction log en cada servidor, en función de la carga transaccional que tenga.

Saludos
Salvador Ramos

Google