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)
September 9th, 2008 a las 11:08 am
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.
September 9th, 2008 a las 2:16 pm
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.
September 9th, 2008 a las 2:17 pm
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
September 9th, 2008 a las 9:58 pm
Gracias por las aportaciones @Bingen! tomo buena nota
September 10th, 2008 a las 7:05 am
[...] « SQL SERVER: Log de Transacciones 10 09 2008 [...]
September 10th, 2008 a las 12:44 pm
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
November 11th, 2008 a las 7:46 pm
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