Mantenimiento al LOG de transacciones ,SQL Server

Si nunca diéramos un mantenimiento al archivo de Log de transacciones este crecería indefinidamente, esto tiene dos implicaciones.

A. El espacio ocupado en disco duro
a. Cuando nuestro archivo Log ha ocupado todo el espacio disponible en el disco duro o ha llegado a su límite de crecimiento establecido el Log ya no puede crecer y si consideramos que cualquier transacción Insert, Delete , update se registra en el Log de transacciones tendremos un problema pues ninguna de estas transacciones podrá ejecutarse en la BD
B. Un problema de timeOut cuando el Log de transacciones necesite crecer y sea ya demasiado grande.

Es conveniente crear un plan de mantenimiento que respalde el Log de transacciones y posteriormente lo trunque, lo cual nos permitirá reducir el tamaño del Log de transacciones.

El siguiente script satisface nuestros requerimientos:


/* 1) Nombre de la BD a utilizar
Nota : El nombre de la BD debera ser re-emplazado por la BD en cuestion */
USE [MyDataBase]

/* 2) Permitirmos que todas las transacciones
en el Buffer pendientes hagan commit */
CHECKPOINT
GO

/* 3)Agregamos un medio de respaldo para almacenar el backup del LOG
Nota: La ruta o dispositivo de respaldo debera ser re-emplazada
por una ruta valida */
Declare @Mes varchar(2) --variable para guardar el mes
,@dia varchar(2) --variable para guardar el dia
,@Anio varchar(4) --variable para guardar el año
,@Fecha varchar(10) --variable para guardar la fecha
,@Archivo varchar(100) --variable para guardar el nombre del archivo
Select @Mes=Day(getdate()),@dia=Month(getdate()),@Anio=Year(getdate())

Select @Fecha= @Mes+ @dia+ @Anio , @Archivo='C:\Respaldos\LogMiBase_db' + @Fecha + '.bak'


/* Eliminamos el dispositivo de respaldo para evitar un error
en caso de que ya existiera */
if Exists (Select * from sys.backup_devices where name='CopiaMiBase_db')
exec sys.sp_dropdevice 'CopiaMiBase_db'

/* Linea para agregar el dispositivo de respaldo
Nota : El nombre del dispositov y/o las caracteristicas del mismo
deberan ser re-emplazadas por las que satisfagan nuestros
requerimientos*/
EXEC sp_addumpdevice 'disk'
,'CopiaMiBase_db'
,@Archivo

/* 4)Respaldamos el Log de transacciones y la BD
Nota: El nombre de la Base de datos y el LOG
debera ser re-emplazado por el nombre real de la BD en cuestion
y por el nombre logico del archivo LOG de transacciones
*/
BACKUP DATABASE [MyDataBase] TO CopiaMiBase_db
BACKUP LOG [MyDataBase] WITH TRUNCATE_ONLY

--5)Establecemos un tamaño adecuado al Log de transacciones
DBCC SHRINKFILE ('MyDataBase_log', 100)
/*[100] debera ser re-emplazado por el tamaño
adecuado para el LOG de la BD en cuestion */

Lo que restaría es crear un JOB que se encarge de ejecutar nuestras sentencias antes vistas. Espero que sea de utilidad este ejemplo, se que en la red existen muchos, pero uno mas no cae mal.