¿Por qué el registro de transacciones sigue creciendo o se queda sin espacio?


207

Esta parece ser una pregunta común en la mayoría de los foros y en toda la web, se hace aquí en muchos formatos que normalmente suenan así:

En SQL Server -

  • ¿Cuáles son algunas de las razones por las que el registro de transacciones crece tan grande?
  • ¿Por qué mi archivo de registro es tan grande?
  • ¿Cuáles son algunas maneras de evitar que este problema ocurra?
  • ¿Qué hago cuando me pongo al día con la causa subyacente y quiero que mi archivo de registro de transacciones tenga un tamaño adecuado?
  0

La respuesta realmente breve es: poner la base de datos en el modo ** Simple ** (no en el modo ** Full **).Si no está realizando varias copias de seguridad del registro de transacciones durante el día entre su copia de seguridad nocturna completa: no necesita el modo ** Completo ** 07 dic. 162016-12-07 14:48:40

  0

@IanBoyd: seguro que esa es la respuesta más simple.La clave, sin embargo, es llegar a lo que eso significa.Golpeé eso en la respuesta.Lamentablemente, muchas personas nunca abordan esto o simplemente pasan a ser simples sin entender por qué.Sin embargo, editaré mi respuesta para golpear el modo simple un poco antes. 07 dic. 162016-12-07 14:53:55

270

Una respuesta más corta:

Probablemente tenga una transacción de ejecución prolongada (¿Mantenimiento de índice? ¿Borrado de lotes grandes o actualización?) O está en el modo de recuperación "predeterminado" (más abajo en lo que se entiende por defecto) de Full y no ha realizado una copia de seguridad de registro (o no los tomas con la frecuencia suficiente).

Si se trata de un problema del modelo de recuperación, la respuesta simple podría ser Cambiar al modo de recuperación Simple si no necesita recuperación en un momento dado y copias de seguridad de registros regulares.Sin embargo, muchas personas responden sin comprender los modelos de recuperación.Sigue leyendo para comprender por qué es importante y luego decide lo que haces.También puede comenzar a realizar copias de seguridad de registros y permanecer en la recuperación Full .

Podría haber otras razones pero estas son las más comunes.Esta respuesta comienza a sumergirse en las dos razones más comunes y le brinda información de fondo sobre por qué y cómo detrás de las razones, así como también explora otras razones.


Una respuesta más larga:¿Qué escenarios pueden hacer que el registro siga creciendo?Hay muchas razones, pero por lo general estas razones son de los siguientes dos patrones: hay un malentendido sobre los modelos de recuperación o hay transacciones de larga ejecución.Siga leyendo para más detalles.

Razón principal 1/2: No entendemos los modelos de recuperación

(Estar en modo de recuperación total y no tomar copias de seguridad de registro : esta es la razón más común: la gran mayoría de las personas que experimentan este problema lo son.)

Si bien esta respuesta no es una inmersión profunda en los modelos de recuperación de SQL Server, el tema de los modelos de recuperación es fundamental para este problema.

En SQL Server, hay tres recovery models :

  • Full ,
  • Bulk-Logged y
  • Simple .

Bulk-Logged por ahora diremos que es un modelo híbrido y la mayoría de las personas que están en este modelo están ahí por una razón y entienden los modelos de recuperación.

Los dos que nos preocupan y su confusión son la causa de que la mayoría de los casos de personas que tienen este problema son Simple y Full .

Intermedio: Recuperación en General

Antes de hablar de modelos de recuperación: hablemos de recuperación en general.Si quieres profundizar aún más con este tema, solo lee Paul Randal's blog y cuantas publicaciones quieras sobre él.Para esta pregunta, sin embargo:

  1. Recuperación tras error/reinicio
    Uno de los propósitos del archivo de registro de transacciones es la recuperación de bloqueo/reinicio .Para el avance y retroceso del trabajo que se realizó (avance/rehacer) antes de un bloqueo o reinicio y el trabajo que se inició pero no terminó después de un bloqueo o reinicio (retroceso/deshacer).Es el trabajo del registro de transacciones ver que una transacción se inició pero nunca finalizó (se revirtió o se produjo un bloqueo/reinicio antes de que se confirmara la transacción).En esa situación, es el trabajo del registro decir "Oye, esto nunca terminó realmente, retrocedamos" durante la recuperación.También es tarea del registro ver que usted terminó algo y que a su aplicación cliente se le dijo que estaba terminada (incluso si aún no se había endurecido en su archivo de datos) y le dijo "Oye ... esto realmente sucedió, vamos a rodar adelante, vamos a hacer que las aplicaciones piensen que fue " después de reiniciar.Ahora hay más pero ese es el propósito principal.

  2. Punto en el tiempo de recuperación
    El otro propósito para un archivo de registro de transacciones es poder darnos la capacidad de recuperar un punto en el tiempo debido a un "oops" en una base de datos o garantizar un punto de recuperación en caso de una falla de hardware que involucre los datos y/o archivos de registro de una base de datos.Si este registro de transacciones contiene los registros de las transacciones que se iniciaron y finalizaron para la recuperación, SQL Server puede y luego usa esta información para obtener una base de datos donde estaba antes de que ocurriera un problema.Pero esa no es siempre una opción disponible para nosotros.Para que eso funcione, tenemos que tener nuestra base de datos en el modelo de recuperación correcto, y debemos realizar copias de seguridad de registros .

Modelos de recuperacion

Sobre los modelos de recuperación:

  • Modelo de recuperación simple
    Entonces, con la introducción anterior, es más fácil hablar del modelo Simple Recovery primero.En este modelo, está indicando a SQL Server: "Estoy muy bien con usted usando el archivo de registro de transacciones de choque y de reinicio de recuperación ..." (¿De verdad no tienen otra opción que hay Busque. ACID properties y que debe tener sentido con rapidez.) " ... pero una vez que ya no lo necesite para ese propósito de recuperación de bloqueo/reinicio, siga adelante y reutilice el archivo de registro ".

    SQL Server escucha esta solicitud en Simple Recovery y solo conserva la información que necesita para realizar la recuperación/bloqueo.Una vez que SQL Server está seguro de que puede recuperarse porque los datos se han endurecido en el archivo de datos (más o menos), los datos que se han endurecido ya no son necesarios en el registro y se marcan para el truncamiento, lo que significa que se reutilizan.

  • Modelo de recuperación completa
    Con Full Recovery , le está diciendo a SQL Server que desea poder recuperar un punto específico en el tiempo, siempre que su archivo de registro esté disponible o en un momento específico cubierto por una copia de seguridad del registro.En este caso, cuando SQL Server llega al punto en el que sería seguro truncar el archivo de registro en el Modelo de recuperación simple, no lo hará.En lugarPermite que el archivo log continúe creciendo.y le permitirá seguir creciendo,hasta que tome una copia de seguridad de registro(o se queda sin espacio en la unidad de archivo de registro) en circunstancias normales.

Cambiar de simple a completo tiene un Gotcha.

Hay reglas y excepciones aquí.Hablaremos sobre transacciones de larga duración en profundidad a continuación.

Pero una advertencia a tener en cuenta para el Modo de recuperación completa es la siguiente: si simplemente cambia al modo Full Recovery , pero nunca realiza una copia de seguridad completa, SQL Servernohonre su solicitud para estar en el modelo Full Recovery .Su registro de transacciones continuará funcionando como lo hizo en Simple hasta que cambie al Modelo de recuperación completa Y tome su primer Full Backup .

El modelo de recuperación completa sin copias de seguridad de registro es malo.

Entonces, ¿esa es la razón más común para el crecimiento descontrolado de registros?Respuesta: Estar en modo de recuperación completa sin tener copias de seguridad de registro.

Esto pasatodosEl tiempo para las personas.

¿Por qué este error es tan común?

¿Por qué sucede todo el tiempo?Debido a que cada nueva base de datos obtiene su configuración de modelo de recuperación inicial mirando la base de datos modelo.

La configuración inicial del modelo de recuperación del modelo es siempre Full Recovery Model - hasta y a menos que alguien cambie eso.Entonces, se podría decir que el "Modelo de recuperación predeterminado" es Full .Muchas personas no lo saben y tienen sus bases de datos ejecutándose en Full Recovery Model sin copias de seguridad de registros, y por lo tanto un archivo de registro de transacciones mucho más grande de lo necesario.Por eso es importante cambiar los valores predeterminados cuando no funcionan para su organización y sus necesidades)

El modelo de recuperación completa con muy pocas copias de respaldo es malo.

También puede meterse en problemas aquí si no realiza copias de seguridad de registros con la frecuencia suficiente.
Hacer una copia de seguridad de registro al día puede sonar bien, hace que una restauración requiera menos comandos de restauración, pero teniendo en cuenta la discusión anterior, ese archivo de registro seguirá creciendo y creciendo hasta que realice las copias de seguridad de registros.

¿Cómo puedo saber qué frecuencia de copia de seguridad de registro necesito?

Debe tener en cuenta su frecuencia de copia de seguridad de registro teniendo en cuenta dos cosas:

  1. Necesidades de recuperación- Esto debería ser lo primero.En el caso de que la unidad que contiene su registro de transacciones se estropee o sufra una corrupción grave que afecte su copia de seguridad del registro, ¿cuántos datos se pueden perder?Si ese número no supera los 10 a 15 minutos, debe realizar la copia de seguridad del registro cada 10 a 15 minutos, al final de la discusión.
  2. Crecimiento de troncos- Si su organización está en condiciones de perder más datos debido a la capacidad de recrear fácilmente ese día, es posible que tenga una copia de seguridad de registro con menos frecuencia que 15 minutos.Tal vez su organización está bien con cada 4 horas.Pero tienes que mirar cuántas transacciones generas en 4 horas.¿Permitir que el registro siga creciendo en esas cuatro horas hace que el archivo de registro sea demasiado grande?¿Significará eso que las copias de seguridad de su registro tardarán demasiado?

Razón principal 2/2: Transacciones de larga ejecución

("¡Mi modelo de recuperación está bien! ¡El registro aún está creciendo!)

Esto también puede ser una causa de crecimiento descontrolado e incontrolado de registros.No importa el modelo de recuperación, pero a menudo aparece como "Pero estoy en el Modelo de recuperación simple, ¡¿por qué mi registro sigue creciendo?"

La razón aquí es simple: si SQL está utilizando este registro de transacciones para fines de recuperación como lo describí anteriormente, entonces tiene que ver al inicio de una transacción.

Si tiene una transacción que lleva mucho tiempo o hace muchos cambios, el registro no se puede truncar en el punto de control para ninguno de los cambios que aún están en transacciones abiertas o que se han iniciado desde que se inició esa transacción.

Esto significa que una eliminación grande, la eliminación de millones de filas en una declaración de eliminación es una transacción y el registro no puede hacer ningún truncamiento hasta que se realice la eliminación completa.En Full Recovery Model , esta eliminación se registra y eso podría ser una gran cantidad de registros.Lo mismo con el trabajo de optimización de índice durante las ventanas de mantenimiento.También significa que una gestión de transacciones deficiente y no observar y cerrar transacciones abiertas realmente puede perjudicarle a usted y a su archivo de registro.

¿Qué puedo hacer con estas transacciones de larga duración?

Puedes salvarte aquí por:

  • Ajustar correctamente el archivo de registro para tener en cuenta el peor de los casos, como su mantenimiento o grandes operaciones conocidas.Y cuando crezcas tu archivo de registro, deberías consultar este guidance (y los dos enlaces a los que te envía) por Kimberly Tripp.El tamaño correcto es super crítico aquí.
  • Viendo su uso de las transacciones.No inicie una transacción en su servidor de aplicaciones y comience a tener largas conversaciones con SQL Server y corra el riesgo de dejar una abierta demasiado tiempo.
  • Observando las transacciones implícitas en tus declaraciones DML.Por ejemplo: UPDATE TableName Set Col1 = 'New Value' es una transacción.No puse un BEGIN TRAN allí y no tengo que hacerlo, todavía es una transacción que se realiza automáticamente cuando se realiza.Así que si realiza operaciones en un gran número de filas, considere agrupar esas operaciones en partes más manejables y dar tiempo de recuperación al registro.O considere el tamaño correcto para lidiar con eso.O quizás busque cambiar los modelos de recuperación durante una ventana de carga masiva.

¿Estas dos razones también se aplican a Log Shipping?

Respuesta corta: si.Respuesta más larga a continuación.

Pregunta: "Estoy usando el envío de registros, por lo que las copias de seguridad de mis registros están automatizadas ... ¿Por qué sigo viendo un crecimiento del registro de transacciones?"

Respuesta: sigue leyendo.

¿Qué es Log Shipping?

El envío de registros es exactamente lo que parece: está enviando las copias de seguridad de sus registros de transacciones a otro servidor para fines de DR.Hay algo de inicialización pero después de eso el proceso es bastante simple:

  • Un trabajo para hacer una copia de seguridad del registro en un servidor,
  • un trabajo para copiar ese registro de copia de seguridad y
  • un trabajo para restaurarlo sin recuperación (ya sea NORECOVERY o STANDBY) en el servidor de destino.

También hay algunos trabajos para monitorear y alertar si las cosas no salen como las tiene planeadas.

En algunos casos, es posible que solo desee realizar la restauración del envío de registros una vez al día o cada tercer día o una vez a la semana.Eso está bien.Pero si realiza este cambio en todas las tareas (incluidas la copia de seguridad de registro y las tareas de copia), eso significa que está esperando todo ese tiempo para realizar una copia de seguridad de registro.Eso significa que tendrá mucho crecimiento de registros, ya que está en modo de recuperación total sin copias de seguridad de registros , y probablemente también signifique un archivo de registro grande para copiar.Solo debe modificar la programación de la tarea de restauración y dejar que las copias de seguridad y copias del registro se realicen con mayor frecuencia, de lo contrario, tendrá el primer problema descrito en esta respuesta.


Solución de problemas generales a través de códigos de estado

Existen otras razones además de estas dos, pero estas son las más comunes.Independientemente de la causa: hay una manera en que puede analizar su razón para este crecimiento/falta de truncamiento de registro inexplicable y ver cuáles son.

Al consultar la vista de catálogo sys.databases , puede ver información que describe la razón por la que su archivo de registro puede estar esperando para truncar/reutilizar.

Hay una columna llamada log_reuse_wait con una ID de búsqueda del código de razón y una columna log_reuse_wait_desc con una descripción de la razón de espera.Del artículo en línea de los libros de referencia se encuentran la mayoría de las razones (las que probablemente verá y las razones por las que podemos explicarlas. Las que faltan están fuera de uso o para uso interno) con algunas notas sobre la espera. cursiva :

  • 0 = Nada
    Lo que suena como ... No debería estar esperando

  • 1 = punto de control
    A la espera de que se produzca un punto de control. Esto debería suceder y usted debería estar bien, pero hay algunos casos que debe buscar aquí para futuras respuestas o ediciones.

  • 2 = copia de seguridad de registro
    Está esperando que se realice una copia de seguridad del registro. O los tiene programados y sucederá pronto, o tiene el primer problema descrito aquí y ahora sabe cómo solucionarlo

  • 3 = Copia de seguridad activa o restauración
    Una operación de copia de seguridad o restauración se está ejecutando en la base de datos

  • 4 = transacción activa
    Hay una transacción activa que debe completarse (de cualquier manera, ROLLBACK o COMMIT) antes de poder hacer una copia de seguridad del registro.Esta es la segunda razón descrita en esta respuesta.

  • 5 = reflejo de la base de datos
    O un espejo se está quedando atrás o por debajo de cierta latencia en una situación de alto rendimiento de reflejo o el reflejo se detiene por alguna razón

  • 6 = Replicación
    Puede haber problemas con la replicación que podrían causar esto, como un agente de lector de registros que no se está ejecutando, una base de datos que piensa que está marcada para la replicación que ya no existe y varias otras razones. También puede ver esta razón y es perfectamente normal porque está mirando el momento justo, justo cuando el lector de registros consume las transacciones.

  • 7 = Creación de instantáneas de la base de datos
    Está creando una instantánea de la base de datos, verá esto si observa el momento justo mientras se crea una instantánea

  • 8 = Log Scan
    Todavía tengo que encontrar un problema con esto para siempre. Si observa el tiempo suficiente y con la frecuencia suficiente, puede ver que esto sucede, pero no debería ser la causa del crecimiento excesivo del registro de transacciones, como he visto.

  • 9 = Una réplica secundaria de AlwaysOn Availability Groups está aplicando registros de transacciones de esta base de datos a una base de datos secundaria correspondiente.Sobre la descripción más clara todavía ...

+1

La división de páginas aumentará el registro.Una razón importante (según mi experiencia) que no se ha mencionado para un gran crecimiento que puede requerir un encogimiento frecuente que se ha resuelto en muchos de mis casos sería el uso de opciones de índice adecuadas, incluida la administración adecuada del factor de relleno.Utilizo los siguientes ajustes, con estrecha observación.Configuraciones FF: (0/100) tablas con lecturas altas/bajas, (90) para ligeramente modificadas, (80) lecturas medianas/bajas medianas, (70) escrituras altas, (60) Apenas puedo llegar a esto Nivel o algo más podría estar mal.Utilice luego las programaciones de gestión de índices que coincidan con el volumen de datos. 08 oct. 152015-10-08 19:31:03


98

Ya que no estoy realmente satisfecho con ninguna de las respuestasover on Stack Overflow, incluida la sugerencia más fuertemente votada, y como hay algunas cosas que me gustaría abordar y que la respuesta de Mike no responde, pensé que también daría mi opinión aquí.También puse una copia de esta respuesta allí.

Hacer que un archivo de registro sea más pequeño realmente debería reservarse para situaciones en las que se encontró un crecimiento inesperado que no espera que vuelva a ocurrir.Si el archivo de registro volverá a tener el mismo tamaño, no se logrará mucho al reducirlo temporalmente.Ahora, dependiendo de los objetivos de recuperación de su base de datos, estas son las acciones que debe tomar.

En primer lugar, tomar una copia de seguridad completa

Nunca realice cambios en su base de datos sin asegurarse de que puede restaurarla si algo sale mal.

Si te importa la recuperación de un punto en el tiempo

(Y con la recuperación de un punto en el tiempo, me refiero a que te importa poder restaurar en otra cosa que no sea una copia de seguridad completa o diferencial).

Presumiblemente tu base de datos está enFULLmodo de recuperación.Si no, entonces asegúrese de que sea:

ALTER DATABASE yourdb SET RECOVERY FULL;

Incluso si está realizando copias de seguridad completas regulares, el archivo de registro crecerá y crecerá hasta que realice unaIniciar sesióncopia de seguridad: esto es para su protección, para no consumir innecesariamente espacio de disco.Debe realizar estas copias de seguridad de registros con bastante frecuencia, de acuerdo con sus objetivos de recuperación.Por ejemplo, si tiene una regla comercial que establece que puede permitirse perder no menos de 15 minutos de datos en caso de un desastre, debe tener un trabajo que respalde el registro cada 15 minutos.Aquí hay una secuencia de comandos que generará nombres de archivos con marca de tiempo según la hora actual (pero también puede hacer esto con planes de mantenimiento, etc., simplemente no elija ninguna de las opciones de reducción en los planes de mantenimiento, son horribles).

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\yourdb_' 
    + CONVERT(CHAR(8), GETDATE(), 112) + '_'
    + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
    + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

Tenga en cuenta que\\backup_share\debe estar en una máquina diferente que represente un dispositivo de almacenamiento subyacente diferente.La copia de seguridad en la misma máquina (o en una máquina diferente que usa los mismos discos subyacentes, o una máquina virtual diferente que está en el mismo host físico) no le ayuda realmente, ya que si la máquina explota, ha perdido su base de datosySus copias de seguridad.Dependiendo de su infraestructura de red, puede tener más sentido hacer una copia de seguridad local y luego transferirla a una ubicación diferente detrás de la escena;en cualquier caso, querrá sacarlos de la máquina de la base de datos primaria lo más rápido posible.

Ahora, una vez que tenga copias de seguridad de registro regulares en ejecución, debería ser razonable reducir el archivo de registro a algo más razonable que cualquier otra cosa hasta ahora.Esto hacenosignifica correrSHRINKFILEuna y otra vez hasta que el archivo de registro sea de 1 MB: incluso si realiza una copia de seguridad del registro con frecuencia, aún debe acomodar la suma de las transacciones simultáneas que puedan ocurrir.Los eventos de crecimiento automático de archivos de registro son costosos, ya que SQL Server tiene que poner a cero los archivos (a diferencia de los archivos de datos cuando la inicialización instantánea de archivos está habilitada), y las transacciones de los usuarios tienen que esperar mientras esto sucede.Desea hacer lo menos posible esta rutina de crecer, reducir, aumentar y reducir, y ciertamente no desea que sus usuarios paguen por ello.

Tenga en cuenta que es posible que deba hacer una copia de seguridad del registro dos veces antes de que sea posible reducirlo (gracias a Robert).

Por lo tanto, necesita crear un tamaño práctico para su archivo de registro.Nadie aquí puede decirle qué es eso sin saber mucho más sobre su sistema, pero si ha estado reduciendo el archivo de registro con frecuencia y ha vuelto a crecer, una buena marca de agua es probablemente un 10-50% más alta que la más grande. .Digamos que se trata de 200 MB, y desea que cualquier evento de crecimiento automático posterior sea de 50 MB, luego puede ajustar el tamaño del archivo de registro de esta manera:

USE [master];
GO
ALTER DATABASE Test1 
    MODIFY FILE
    (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

Tenga en cuenta que si el archivo de registro es actualmente> 200 MB, es posible que deba ejecutar esto primero:

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

Si no te importa la recuperación de un punto en el tiempo

Si esta es una base de datos de prueba, y no le importa la recuperación de un punto en el tiempo, entonces debe asegurarse de que su base de datos esté enSIMPLEmodo de recuperación.

ALTER DATABASE yourdb SET RECOVERY SIMPLE;

Poner la base de datos enSIMPLEel modo de recuperación se asegurará de que SQL Server reutilice partes del archivo de registro (esencialmente eliminando las transacciones inactivas) en lugar de crecer para mantener un registro detodostransacciones (comoFULLla recuperación hace hasta que usted copia el registro).CHECKPOINTlos eventos ayudarán a controlar el registro y se asegurarán de que no sea necesario crecer a menos que genere mucha actividad t-log entreCHECKPOINTs.

A continuación, debe estar absolutamente seguro de que el crecimiento de este registro fue realmente debido a un evento anormal (por ejemplo, una limpieza de primavera anual o la reconstrucción de sus índices más grandes), y no debido al uso diario normal.Si reduce el archivo de registro a un tamaño ridículamente pequeño, y SQL Server solo tiene que volverlo a aumentar para adaptarse a su actividad normal, ¿qué ganó?¿Pudiste hacer uso de ese espacio en disco que liberaste solo temporalmente?Si necesita una solución inmediata, puede ejecutar lo siguiente:

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
-- 200 MB
DBCC SHRINKFILE(yourdb_log, 200);
GO

De lo contrario, establecer un tamaño apropiado y la tasa de crecimiento.Según el ejemplo en el caso de recuperación de un punto en el tiempo, puede usar el mismo código y lógica para determinar qué tamaño de archivo es el adecuado y establecer parámetros razonables de crecimiento automático.

Algunas cosas que no quieres hacer

  • Copia de seguridad del registro conTRUNCATE_ONLYopción y luegoSHRINKFILE.Para uno, esteTRUNCATE_ONLYLa opción ha quedado en desuso y ya no está disponible en las versiones actuales de SQL Server.Segundo, si estas enFULLmodelo de recuperación, esto destruirá su cadena de registro y requerirá una nueva copia de seguridad completa.

  • Separar la base de datos, eliminar el archivo de registro y volver a adjuntar.No puedo enfatizar lo peligroso que puede ser esto.Es posible que su base de datos no vuelva a aparecer, puede aparecer como sospechoso, es posible que tenga que volver a una copia de seguridad (si tiene una), etc.

  • Utilice la opción "reducir la base de datos".DBCC SHRINKDATABASEy la opción del plan de mantenimiento para hacer lo mismo son malas ideas, especialmente si realmente solo necesita resolver un problema de registro.Apunte el archivo que desea ajustar y ajústelo de forma independiente, usandoDBCC SHRINKFILEoALTER DATABASE ... MODIFY FILE(ejemplos anteriores).

  • Reducir el archivo de registro a 1 MB.Esto parece tentador porque, hey, SQL Server me permite hacerlo en ciertos escenarios, ¡y mira todo el espacio que libera!A menos que su base de datos sea de solo lectura (y así sea, debe marcarla como tal usandoALTER DATABASE), esto simplemente conducirá a muchos eventos de crecimiento innecesarios, ya que el registro debe acomodar las transacciones actuales independientemente del modelo de recuperación.¿Cuál es el punto de liberar ese espacio temporalmente, solo para que SQL Server pueda recuperarlo lenta y dolorosamente?

  • Crear un segundo archivo de registro.Esto proporcionará alivio temporal para la unidad que ha llenado su disco, pero es como tratar de reparar un pulmón perforado con una curita.Debe tratar el archivo de registro problemático directamente en lugar de simplemente agregar otro problema potencial.Además de redirigir alguna actividad de registro de transacciones a una unidad diferente, un segundo archivo de registro realmente no hace nada por usted (a diferencia de un segundo archivo de datos), ya que solo se puede usar uno de los archivos a la vez.Paul Randal also explains why multiple log files can bite you later.

Ser proactivo

En lugar de reducir su archivo de registro a una cantidad pequeña y dejarlo crecer automáticamente a una pequeña tasa por sí solo, configúrelo en un tamaño razonablemente grande (uno que se ajuste a la suma de su mayor conjunto de transacciones concurrentes) y establezca un crecimiento automático razonable la configuración como un respaldo, para que no tenga que crecer varias veces para satisfacer transacciones individuales y para que sea relativamente raro que tenga que crecer durante las operaciones comerciales normales.

Las peores configuraciones posibles aquí son 1 MB de crecimiento o 10% de crecimiento.Curiosamente, estos son los valores predeterminados para SQL Server (de los que me he quejado yasked for changes to no avail) - 1 MB para archivos de datos y 10% para archivos de registro.El primero es demasiado pequeño en esta época, y el último conduce a eventos más y más largos cada vez (por ejemplo, su archivo de registro es de 500 MB, el primer crecimiento es de 50 MB, el siguiente crecimiento es de 55 MB, el próximo crecimiento es de 60.5 MB , etc. etc. - y en I/O lento, créame, realmente notará esta curva).

Otras lecturas

Por favor, no te detengas aquí;Si bien muchos de los consejos que ve sobre cómo reducir los archivos de registro son intrínsecamente malos e incluso potencialmente desastrosos, hay algunas personas que se preocupan más por la integridad de los datos que por liberar espacio en el disco.


21

También puede ver el contenido de su archivo de registro.Para ello, puedes utilizar los indocumentados.fn_dblog, o un lector de registro de transacciones, comoApexSQL Log.

No muestra la reorganización del índice, pero muestra todos los eventos DML y varios DDL:ALTER,CREATE,DROP, activador habilitar/deshabilitar, otorgar/revocar permisos, cambiar el nombre del objeto.

ApexSQLLogProject.temp - ApexSQL.log

Descargo de responsabilidad: trabajo para ApexSQL como ingeniero de soporte


1

Este es el problema que se enfrenta con mayor frecuencia en casi todos los DBA donde los registros crecen y llenan el disco.

• ¿Cuáles son algunas de las razones por las que el registro de transacciones crece tanto?

  1. Transacción activa larga
  2. Transacciones de alto registro como reconstrucción de índice, reorganización, inserción masiva, eliminaciones, etc.
  3. Cualquier HA como replicación, creación de reflejo que contenga el registro y no le permita liberar el espacio de registro

• ¿Por qué mi archivo de registro es tan grande?

Verifique la columna log_reuse_wait_des c en la tabla sys.databases para saber de qué se están truncando los registros:

select name, log_reuse_wait_desc 
from sys.databases

• ¿Cuáles son algunas maneras de evitar que este problema ocurra?

Las copias de seguridad de los registros lo ayudarán a controlar el crecimiento de los registros a menos que haya algo que impida que los registros se vuelvan a utilizar.

• ¿Qué hago cuando me pongo al día con la causa subyacente y quiero que mi archivo de registro de transacciones tenga un tamaño adecuado?

Si ha identificado qué es lo que realmente lo está causando, intente solucionarlo según se explica en la página siguiente.

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

Tener programadas las copias de seguridad de registros adecuadas es la mejor manera de lidiar con el crecimiento de registros a menos que se trate de una situación inusual.