Controlar el tamaño del registro de transacciones en SQL Server

Es probable que si no gestiona bien el registro de transacciones de sus bases de datos, observe que el tamaño de éste sea mucho más grande que los archivos de datos de la base de datos.

¿Cómo funciona el registro de transacciones?

Cuando una aplicación cliente, lanza una transacción sobre la base de datos, se ejecuta un proceso de preescritura de la transacción en el registro (para que en caso de que se caiga SQL Server, la base de datos la encontremos en un estado coherente al levantar el servicio). Las transacciones que estén bien escritas, son escritas a disco y pasan al estado de inactividad cuando pasa un punto de comprobación.

Dependiendo del modelo de recuperación de la base de datos, ocurre una cosa u otra con esas transacciones inactivas. Si el modelo de recuperación es sencillo, automáticamente las transacciones inactivas se eliminan del registro, lo que provocará que el tamaño del registro de transacciones sea pequeño, pero claro, toda la carga transaccional que está ocurriendo en la base de datos, no está almacenada en ninguna parte, con lo que si pierde la base de datos y necesita recuperarla, tendrá que hacerlo desde la última copia completa o diferencial de base de datos, perdiendo así toda la carga transaccional que ocurrió desde la copia de seguridad.

Debe tener en cuenta, que como todas las transacciones se eliminan de forma automática del registro en cuanto pasan al estado de inactividad, no podrá realizar copias de seguridad del registro de transacciones.
Si el modelo de recuperación es completo, las transacciones que pasan al estado de inactividad permanecen en el registro, lo cual puede provocar que el tamaño del registro de transacciones crezca enormemente si no se trata correctamente.

Nota: Si en su base de datos tiene seleccionado el modelo de recuperación completo (con el sencillo no se puede), toda la carga transaccional que ocurra desde la última copia, se irá registrando en el registro de transacciones, por lo que si pierde la base de datos porque la unidad de disco donde estaban los archivos de base de datos, intente hacer una copia de registro de transacciones de la base de datos (en estado sospechoso) si el registro de transacciones está en una unidad disponible.

BACKUP LOG BASEDEDATOS TO DISPOSITIVO WITH NO_TRUNCATE

Así, a la hora de recuperar la base de datos, restaure la copia de seguridad completa de base datos moviendo la ubicación de los archivos y la copia de registro de transacciones que acaba de hacer.

Volvamos a lo que nos ocupa: el registro de transacciones tiene un tamaño grandísimo.
Si en su estrategia de copia de seguridad, solo incluye copias completas, éstas, no afectan para nada al registro, por lo que seguirá creciendo.

Entonces, ¿Cómo hacemos para disminuir ese tamaño?

La respuesta está en modificar la estrategia de copia de seguridad.
Al hacer una copia de seguridad del registro de transacciones, como se está sacando toda la información a una copia, SQL Server, trunca toda la parte inactiva del registro de transacciones, provocando así que el tamaño utilizado de la base de datos disminuya bruscamente.

Por lo tanto, al hacer una copia de seguridad de registro de transacciones (la cual necesita una copia completa como soporte), reducirá el tamaño utilizado del registro y encima, tendrá una copia de seguridad del registro, lo cual le permitirá recuperar la base de datos hasta el momento en el que realizó la copia del registro.

Como observará el proceso es como la pescadilla que se muerde la cola y con esto quiero decir, que cuantas más copias de registro haga durante el día, como el intervalo entre copias es pequeño, tanto el tamaño de las copias, como el tiempo en el que se realizan es pequeño y además, el tamaño del registro de transacciones también lo será.

Nota: Al hacer la copia del registro de transacciones, se reduce el espacio utilizado por el registro, pero no se libera a disco, es decir, imagine que tiene un registro de transacciones de 4 GB y decide hacer una copia de registro. La copia, tardará en hacerse porque se ha desbocado el tamaño del registro, tendrá un tamaño importante y el archivo de registro, seguirá ocupando 4 GB, lo que ha cambiado es que de esos 4 GB, se estará usando un 1%. SI desea liberar el espacio a disco, debe realizar una operación de reducción de archivo de log, desde las opciones de reducción.

Conclusión

En las base de datos de lectura o bases de datos de prueba en las que no le importe que se pierdan datos, use el modelo de recuperación sencillo.

En las bases de datos de producción, use el modelo completo, pero agregue copias de seguridad de registro de transacciones a su estrategia de copia de seguridad, lo que le permitirá recuperar la base de datos sin mucha perdida de datos y además, controlará en tamaño usado del registro de transacciones.

Tenga en cuenta que en el proceso de restauración, deberá seguir la secuencia lógica, es decir, si ha hecho una copia completa por la noche y durante el día 10 de registro cada hora y quiere recuperar el sistema, deberá recuperar la copia completa y la secuencia completa de registro, sin omitir ninguna. Es por eso, que le podría interesar después de 3 o cuatro copias de registro, agregar una copia diferencial para que los procesos de recuperación no sean tan “pesados”.

Arriba