El Transaction Log de SQL server es uno de los quebraderos de cabeza para muchos administradores noveles que no cuentan con un buen plan de mantenimiento o no conocen a fondo el funcionamiento de SQL como para reducirlo a mano. En este post trataré de explicar y dar algunas soluciones rápidas para evitar el aburrimiento. A quien quiera información más detallada le recomiendo los Libros en Pantalla de SQL o Googlear un poco que nunca está de más 
.
¿Qué es el Transaction Log?
Por cada bbdd (archivo .mdf) se crea un archivo de log (archivo .ldf) en el que se almacenan todos los cambios que se producen en la bbdd. En él se van guardando cambios que luego permitirán volver atrás (rollback) en una transacción o incluso hacer una recuperación a un estado anterior.
¿Por qué puede ocupar mucho más que la propia bbdd?
Cuando se ejecutan muchas consultas y/o afectan a un gran número de registros, produciendo cambios en la bbdd. Todos los cambios se van almacenando en el transaction log, si además no tenemos un plan de mantenimiento que reduzca este log de forma periódica el .ldf se irá llenando hasta ocupar una gran cantidad de espacio y terminará por llenar el disco.
¿Cómo se reduce el log de transacciones?
Existen varias maneras, la forma más recomendable es utilizando un plan de mantenimiento que realice una copia de seguridad del registro de transacciones. A continuación vamos a ver algunos ejemplos y posibilidades:
- Backup log: Guardamos un backup y luego reducimos el log de la bbdd “sergiodb” a 10 Mb
USE [sergiodb] – Utilizamos la bbdd sergiodb
CHECKPOINT – Para que todas las páginas de memoria se escriban en la bd
GOEXEC sp_addumpdevice ‘disk’ ,‘CopiaMiBase_sergiodb’ ,‘c:\LogMiBase_sergiodb.bak’ – Creamos un punto donde guardaremos el log y procedemos a hacer el backup con truncado del log
BACKUP DATABASE sergiodb TO CopiaMiBase_sergiodb
BACKUP LOG pruebamm WITH TRUNCATE_ONLY
DBCC SHRINKFILE (‘sergiodb_log’, 10) –- Dejamos el archivo de log con un tamaño de 10 Mb
- Reducir sin hacer backup:
USE [sergiodb] – Utilizamos la bbdd sergiodb
CHECKPOINT – Para que todas las páginas de memoria se escriban en la bd
GO
BACKUP
LOG sergiodb WITH TRUNCATE_ONLY – Truncamos el registro
DBCC SHRINKFILE (sergiodb _Log, 10) – Lo reducimos a 10 Mb
- Eliminar el archivo de log para que se genere de nuevo: Esta solución es más arriesgada, aunque a veces es necesario tirar de ella por ejemplo cuando el log es tan grande que el servidor no tiene espacio o memoria como para gestionarlo.
ALTER DATABASE [sergiodb] SET SINGLE_USER; – Ponemos la bbdd en single user
GO
sp_detach_db ‘sergiodb’ – Separamos la bbdd
Ahora es necesario localizar el archivo de log, en mi caso C:\Archivos de programa\SWsoft\Plesk\Databases\MSDE\MSSQL\Data\sergiodb_log.ldf y eliminarlo. Luego ejecutamos esta nueva consulta para adjuntarla de nuevo.
USE [master]
CREATE</SPANDATABASE [sergiodb] ONFILENAME=(‘C:\Archivos de programa\SWsoft\Plesk\Databases\MSDE\MSSQL\Data\sergiodb.mdf’),
(FILENAME=‘C:\Archivos de programa\SWsoft\Plesk\Databases\MSDE\MSSQL\Data\sergiodb_log.LDF’)
FOR ATTACH
ALTER DATABASE sergiodb SET MULTI_USER; – Ponemos la bbdd en multi user
Entradas (RSS)
Sin duda, tengo que buscarme un plugin para poner código :S
A quien copie las lineas que tenga en cuenta que debe quitar los saltos de línea que ha incluido WP en mitad de algunas sentencias…
En cualquier caso, a ver si a lo largo de esta semana encuentro algun plugin que me sea útil para mostrar código.
lo unico recordar que si no necesitamos hacer backup del log (solo hacemos bk completos por la noche, por ejemplo) y eso nos vale, una vez hemos hecho que el log ocupe poco, lo mejor será poner el modo de recuperación de la base de datos en modo simple, y asi el log no volverá a crecer hasta el infinito.
y hay que fiarse un poco mas del sql server, si ya separas el checkpoint del backup (instrucción GO), no debería hacer falta insistir tanto ;-D
Gracias por las aportaciones @Bingen! tomo buena nota
[...] « SQL SERVER: Log de Transacciones 10 09 2008 [...]
Post editado… He hecho caso al maestro, y nunca mejor dicho porque en efecto ha sido mi maestro
Pd: Tengo que hacer algo ya con el estilo para el código… :S
Tengo dos Tablas en SQL Server2000, una de las tablas fue borrada y a la otra le alteraron datos, ya logré restablecer la información, pero quisiera saber quien borró y modificó los datos. Existe alguna consulta que me lo permita saber?
Gracias
Recuerden tambien que si tienen creda una publicacion en la base de datos,tienen configurada la replica, a la cual le quieren reducir el log de esta manera que explica sergio ,ejecutaran el transact pero se les matara de la risa ,el archivo no se reducira,deben borrar la publicacion y despues correr el script.
yo tambien espero respuesta para la pregunta de ALejandro Bejarano .. como detectar cambios producidos por un usuario X.. ya que administro una BD con 10 usuarios registrados.
@Damian Ferreyra gracias por el aporte
.
@Alejandro Bejarano, @mario me temo que la auditoria de SQL Server 2000 se sale del propósito del post. Quizá este enlace os pueda servir http://technet.microsoft.com/en-us/library/dd277388.aspx
Aúpa Sergio, yo también tuve un problema con sqlserver y su fichero de transacciones, y lo solucione así (básicamente lo reduzco a hacer el detach y borrar el log): http://febrer.wordpress.com/2009/06/19/como-borrar-el-registro-de-transacciones-transaction-log-en-sql-server/
A mi entender SQL Server e sun desastre como base de datos.
No puede ser que algo como un registro de transacciones acupe mas que la base de datos en sí !!!!.. es ridículo !!!!…
Hace tiempo trabajo con MySQL y Postgre y la verdad es que problemas de éste tipo “ridículos” nunca encontré. A parte de ser motores potentes y confiables. Quizás sea un buen momento para ver nuevas tecnologías.
p.d: Es solo mi opinión
Saludos.
Daniel.
Ayuda….
En donde trabajo tenemos una base de datos hace poco comenzo a mandar la leyenda de LA BASE DE DATOS NO ESTA DISPONIBLE POR QUE EL ARCHIVO DE TRANSACCIONES ESTA LLENO, HAGA UNA COPIA DE EL PARA LIBERAR ESPACIO, eso lo manda a la hora de logearte a la pagina o cuando quieres subir algunos archivos planos a la base de datos, busque informacion e hice esto:
use mibase
checkpoint
BACKUP LOG mibase TO disk=’C:\copiademibase.bak’
Lo hace bien, pero se vuelve a llenar luego luego a los dos dias y sinceramente no se hacen muchas transacciones, el archivo log de esa base de datos pesa 504kb y de ahi no crece no se por que, alguien me podria decir que puedo hacer por que sinceramente ya me harte de este problema.
Intente hacer un detach y borrar el archivo log, pero cuando adjunto la base de datos e intento logearme no hace nada, en el administrador corporativo la base de datos apararece como sospechosa cuando borro el archivo log,
ALGUIEN QUE PUEDA AYUDARME, SALUDOS
Alejandro Bejarano: No puedes saber directamente quien te borro o altero los datos, tendrias que “leer” el log de transacciones, ¿como?, con herramientas de terceros como LOG EXPLORER.
NeOleon: Establece un PLAN DE MANTENIMIENTO, que no te lleva arriba de 5 minutos y te quitas definitivamente del “problema”, o como bien dicen por aqui, si no vas a requerir de una recuperacion directa de tus transacciones, cambia el modo de tu base a SIMPLE y problema resuelto
Daniel:
Todas las opiniones se respetan, siempre y cuando no afecten directamente la forma de pensar de otros, “SQL Server es un desastre”, bien, seria bueno saber porque tienes ese argumento.
Creo que SERGIO SAINZ, hace un buen resumen cuando dice:
“El Transaction Log de SQL server es uno de los quebraderos de cabeza para muchos administradores noveles que no cuentan con un buen plan de mantenimiento o no conocen a fondo el funcionamiento de SQL”
Desconocimiento de la herramienta, es la palabra.
Como reduzco el Log en SQL Server 2008, ya no tiene la funcion TRUNCATE_ONLY, no he podido encontrar nada referente a esto.
Gracias.
Victor
Si tu version es 2005 u 2008, el truncate del Log, esta implicito si haces un simple backup del mismo
Hola como puedo en una base de datos mdf de sql server, utilizar multiples archivos de log LDF ?
Yo use el siguiente codigo que es muy efectivo:
checkpoint
go
checkpoint
go
BACKUP LOG BAS WITH TRUNCATE_ONLY
go
DBCC SHRINKFILE (BAS_LOG, 10)