Si te haces esta pregunta estás ante lo más común cuando comienzas a administrar SQL, la respuesta fácil sería darte dos comandos, y que luego funcionen o no. La verdad no me interesa eso en absoluto, creo que debes entender como funciona el log para saber porque crece, así entenderás porque aunque hagas backups todas las noches tu log sigue creciendo, y además, y mucho mejor sabrás como evitar que crezca si no tiene que hacerlo.

Para comenzar, que el log crezca depende directamente del modo de recuperación que tenga tu base de datos, solo en los modos FULL y BULK LOGGED crecerá sin medida (también puede crecer en el modo simple, es mucho más raro pero puede suceder, en 3 lineas veremos porqué).

El modo de recuperación de la bbdd puedes verlo en las propiedades (SQL Server Management Studio, te conectas al relacional, propiedades.. y en la pesstaña opciones aparece el modo de recuperación actual y en el desplegable el resto de modos. Yo tengo el cliente en inglés, así que disculpadme pero lo vereis en inglés

 El log de transacciones se encarga de registrar una tras otras de forma secuencial todas las modificaciones que se producen en la base de datos, esto es, la información que después ha de consolidarse en las tablas.  De esta forma el fichero MDF  (o los NDF’s ) no tienen porque contenter toda la información actualizada en un momento dado. Así pues, hagais lo que hagais NO BORREIS EL FICHERO DE LOG, que además de ser una barvaridad, os hará seguramente tener inconsistencias en la base de datos. Es más no se puede recuperar sin un comando totalmente indocumentado que usa soporte de microsoft.

Si es una estructura secuencial, y registra los cambios ¿debe crecer infinitamente?, la respuesta es depende, depende del modo de recuperación , si estamos en modo simple, en cuanto un proceso de baja prioridad que tiene SQL consolida la información del log en los datos pasa y salvaguarda los datos, esa información se puede sobre-escribir. ¿hay excepciones? SI, no se puede sobreescribir si hay una transacción antigua abierta, es decir si alguien hace, BEGIN TRAN, Update Tabla SET Campo=VAlor WHERE condicion, y se olvida de ejecutar el COMMIT TRAN o el ROLLBACK, sql no puede rehusar el trozo de log, porque no sabe que va a pasar con esa transacción y por tanto el fichero seguirá creciendo…  Si el modo de recuperación es sencillo se puede reaprovechar el log, como acabamos de ver, sin embargo, si algo falla, solo podremos recuperar el último backup completo (ningún backup del log puede garantizar que no se hallan reaprovechado trozos, y por tanto son inútiles, tanto que SQL no deja ni hacerlos ).

En el modo de recuperación completo y bulk logged solo se reaprovecharán estos trozos, si además de no quedar transacciones antiguas abiertas, se ha hecho copia de seguridad DEL LOG, es decir no BACKUP DATABASE sino BACKUP LOG.

Así pues ¿porque crece mi log de transacciones?,

Puede ser porque : Tienes una transacción muy antigua abierta (compruebalo con DBCC OPENTRAN en la BBDD que quieras hacer la operación), el comando devolverá algo así

Oldest active transaction:

SPID (server process ID): 53

UID (user ID) : -1

Name : user_transaction

LSN : (33:452:2)

Start time : Jan 5 2010 1:34:24:870AM

SID : 0x0105000000000005150000006ae2268ca3bb996b6e0e17c6e8030000

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 Si no es esto ni la hubo, puede ser porque no hayas hecho nunca copias de seguridad del log de transacciones, en cuyo caso no se reaprovecha el trocito del log.

Bueno, ya sabes porque crece, pero ya ha crecido y ahora ¿como lo reduzco?.

Bueno, el tamaño del log depende del uso de la BBDD así que no hay una regla fija, pero vamos entre el 5 y el 40% de la BBDD es “aceptable” más es muy grande, y menos es muy raro, lo normal entre el 10% y el 15%, aunque insisto, que esto no te obsesione, que no hay reglas fijas.

Si tu tamaño es demasiado grande, reducirlo puede ser una tarea sencilla, prueba los siguientes pasos :

1.- Cambia a modo de recuperación Simple

2.- Ve al menú shrink y reduce el fichero de la bbdd.

¿puede que no se reduzca ni aún así? Si, puede que no se reduzca, el log es una estructura circular y no quita espacio del principio del fichero, sino solo del final así pues lo suyo es que reorganize las páginas antes.

Con esto en principio debiera reducirse, ahora, para que no vuelva a crecer, recuerda, o bien modo de recuperación sencillo (tendrá el tamaño de la transacción abierta que más haya durado y en la que concurrentemente más cosas se hayan hecho) o bien modo completo pero con copias de seguridad frecuentes. (varias al dia es lo habitual).

Saludos