saber - Enorme registro de transacciones con base de datos de SQL Server en modo de recuperación simple
log de transacciones sql server (5)
Como se dijo en otras respuestas, las transacciones activas y las replicaciones son causas típicas de este problema.
Otra, menos visible, es Change Data Capture (CDC).
Recientemente tuve un problema similar y el procedimiento que me permitió liberar el registro fue el siguiente:
- Deshabilitar CDC:
EXEC sys.sp_cdc_disable_db - Cree una publicación en una tabla arbitraria dentro de la base de datos en cuestión
- Eliminar esta / todas las publicaciones.
EXEC sp_removedbreplication ''my_db''es una forma conveniente de hacerlo. - Reducir el registro como se desee
No estoy seguro de por qué fue necesaria la creación / eliminación de esta publicación ficticia / nunca utilizada, pero así fue. Tentativamente, la base de datos puede haber tenido publicaciones anteriores que no se eliminaron correctamente (se dice que ocurre con frecuencia con bases de datos restauradas desde una copia de seguridad anterior).
Otro lenguaje de diagnóstico útil es verificar el log_reuse_wait_desc en sys.databases para la base de datos ofensiva. Este campo lee REPLICATION hasta que REPLICATION el procedimiento anterior.
SELECT log_reuse_wait_desc, * FROM sys.databases where name = ''my_db''
Tengo una base de datos SQL Server bastante grande que utiliza el modo de recuperación SIMPLE. Realmente no tenemos la necesidad de recuperar la segunda recuperación, así que preferiría que nos mantuviéramos en este modo.
Por alguna razón, el registro de transacciones para esta base de datos es masivo (410 GB) con el 99% del espacio sin asignar.
He intentado reducir el tamaño del archivo usando (DBCC SHRINKFILE (MyDatabase_log, 20000)) pero no parece funcionar.
¿Alguien tiene algún consejo sobre por qué una base de datos en modo de recuperación SIMPLE tendría un archivo tan grande? Realmente me gustaría hacerlo encogido.
Replicación del editor? ¿Podría ser esta la razón del enorme registro de transacciones?
Si solo tiene un archivo mdf y un archivo de registro, tal vez la forma más sencilla sea separar la base de datos, cambiar el nombre del registro y volver a adjuntar la base de datos. SQL Server creará un nuevo archivo de registro. Después de eso, su gran archivo de registro se puede eliminar de forma segura.
Aunque esto no funcionará si tiene varios archivos de datos.
Significa que una vez tuvo una sola transacción que duró tanto tiempo que obligó al registro a crecer 410GB. El registro no se puede reutilizar si hay una transacción activa ya que la información de retrotracción no se puede borrar. Tal ejemplo sería si alguien abre una consulta SSMS, inicia una transacción, actualiza un registro y luego se va de vacaciones. La transacción se activará y forzará el crecimiento del registro hasta que finalmente se confirme o se deshaga. Cuando la transacción finalmente termina, el espacio utilizado puede finalmente ser reclamado, dejando un enorme archivo de registro vacío.
Otro escenario es si tuviera aproximadamente 200 GB de datos actualizados en una sola transacción. El registro almacenará las imágenes antes y después de los cambios, lo que consumirá el doble de espacio y no podrá reutilizarse, nuevamente porque es una única transacción.
Actualizar
Olvidé mencionar la Replicación, que también es un factor que puede evitar el truncamiento del registro. Y también lo es Mirroring, una transacción distribuida (técnicamente es lo mismo que una ''transacción activa'', pero la implicación de DTC hace que sea un caso distinto). La lista completa y las explicaciones se encuentran en Factores que pueden demorar el truncamiento del registro .
Te estás perdiendo un argumento en el dbcc shrinkfile :
dbcc shrinkfile (MyDatabase_log, 20000, TRUNCATEONLY)
NOTRUNCATE es el valor predeterminado, que mueve los bloques asignados al principio del espacio no asignado. TRUNCATEONLY elimina el espacio no asignado. Así que si haces un NOTRUNCATE seguido de un TRUNCATEONLY , obtienes un registro reducido.