Cómo reducir un archivo de registro de transacciones de la base de datos de Microsoft SQL Server

Los archivos de registro de la base de datos de Microsoft SQL Server pueden aumentar de tamaño, lo que provoca que la base de datos quede offline y no esté disponible si la ubicación de la unidad de registro de transacciones se queda sin espacio. A continuación, se explica cómo reducir el archivo de registro para reducir el espacio utilizado y resolver el problema.

Hola, mi nombre es Curt. Soy ingeniero principal sénior que trabaja con el equipo de GSE.

Este video está dedicado a demostrar cómo reducir un archivo de registro de transacciones de una base de datos de Microsoft SQL Server. Revisaremos las diferentes formas de reducir el registro de transacciones y qué hacer si la operación de reducción del registro no se ejecuta según lo esperado.

Tenemos un par de maneras diferentes de reducir el archivo de registro. Podemos ir a SQL Server Management Studio. Vaya a las propiedades de la base de datos y realice la operación de reducción allí y vamos a demostrarlo en primer lugar.

Luego también tenemos la operación de hacer el comando TSQL ShrinkFile. La otra cosa que queremos analizar en este video en particular es qué sucede si la operación de reducción del archivo no se ejecuta según lo esperado, y vamos a analizar las opciones que querrá tomar en ese caso. Sigamos adelante y entremos en Studio Management.

Vamos a trabajar con la base de datos 'AdventureWorks2017' y si vamos a 'Tareas' en el menú de la base de datos y bajamos a 'Reducir' tenemos dos opciones diferentes.

Podríamos trabajar a nivel de base de datos tratando con todos los archivos dentro de la base de datos o tratando con archivos específicos, y en este caso definitivamente queremos ir con archivos específicos. Ahora, de manera predeterminada, verá, si observa nuestra paleta aquí, tenemos 'Data' que se muestra en 'File type'.

No vamos a hacer nada con el archivo de la base de datos, sino que buscamos reducir el registro de transacciones, por lo que el registro de transacciones es literalmente 'Log' en el 'Tipo de archivo'. Ahora, tenemos algo de información sobre la base de datos.

El registro de la base de datos es bastante pequeño, esta es una base de datos nueva, y vemos el tamaño que está asignado a ese registro de transacciones, y el espacio libre. El espacio libre puede ser importante porque sin espacio libre no podemos ejecutar una operación de reducción de archivos en una base de datos.

Existen varias opciones diferentes. No vamos a entrar en muchos detalles aquí, pero tenemos la opción de liberar el espacio no utilizado, lo que básicamente trunca el registro de transacciones. Podríamos ejecutar esto simplemente haciendo clic en OK y se realizaría una reducción o un intento de reducción en esa base de datos. En lugar de eso, vamos a seguir adelante y echar un vistazo a la opción TSQL para reducir la base de datos.

Hoy me da un poco de pereza, así que voy a ir hasta 'Script Action to Clipboard', que va a extraer la transacción, el comando TSQL que se habría ejecutado si hubiéramos hecho clic en OK aquí.

Voy a seguir adelante y hacer clic en cancelar, y voy a seguir adelante y pegar el comando TSQL para hacer una reducción en nuestra base de datos AdventureWorks2017. Ahora, para revisar el comando una vez más, es un comando de reducción de archivo. Estamos lidiando con el registro de AdventureWorks2017 y podemos especificar qué tan pequeño queremos que sea ese registro.

En este caso, lo mantendremos en el valor predeterminado de cero. Si no define nada aquí, o deja el valor en cero, intentará reducir el archivo al tamaño original. Tenemos la opción "truncateonly" que permite liberar espacio dentro del registro de transacciones al SO.

A medida que lo ejecutamos, ahora tenemos un informe de lo que hizo y el tamaño actual. Así que siguió adelante y lo redujo a alrededor de un mega, lo cual es genial para nosotros. Recuperamos mucho espacio de disco adicional allí. Una de las cosas que también queremos analizar en este video es qué sucede si ejecutamos shrink file y no se ejecuta correctamente.

La razón número uno por la que se produciría un error en la operación de reducción de archivos es porque no hay suficiente espacio libre dentro de la base de datos para ejecutar el comando de reducción de archivos. Lo primero que querremos hacer es averiguar la cantidad de espacio libre dentro del archivo.

Ahora, lo vimos a través del menú de la GUI, allí también podemos ejecutar este comando en particular, ahora que hemos cambiado nuestro enfoque aquí, estamos trabajando con la base de datos AdventureWorks2019 en su lugar. Si ejecutamos este comando en particular, el comando select, obtendremos la cantidad de espacio libre que se ve en esa base de datos.

Actualmente, vemos tres megas de espacio libre dentro de la base de datos. Hay dos opciones diferentes que podríamos usar para intentar reducir la base de datos en el caso de que nos hayamos quedado sin espacio libre dentro de esa base de datos en particular. Podemos cambiar la opción, la opción de recuperación, para la base de datos de Adventure Works 2019.

Si cambiamos la opción de recuperación a "simple" y ejecutamos el comando dbcc shrink file, debería reducir el archivo a un tamaño mucho más pequeño que el tamaño original. Cada vez que ponemos nuestra base de datos en modo simple, el registro de transacciones se ocupa de transacciones activas y todo lo que no está activo se elimina del registro.

Podemos ejecutar este comando en particular. La mayoría de las bases de datos de producción estarán en recuperación completa, por lo que ir a simple nos permite, o al menos nos da la oportunidad, de ejecutar el comando shrink file con éxito. Estamos en modo simple y, una vez más, nuestra situación es que no pudimos ejecutar esto correctamente.

Después de pasar al modo simple, ejecutamos la operación de reducción de archivo de sincronización con éxito, nos da la información sobre el nuevo tamaño del archivo, y lo último que querríamos hacer en ese caso particular es volver a ponerlo en modo de recuperación completa.

En este punto, tomamos un archivo de registro transaccional inmanejable en términos de usar el comando shrink file y lo llevamos a un estado en el que podemos reducir ese archivo y lo volvimos a poner en un modo en el que ahora se puede respaldar para asegurarnos de obtener la información de transacción más reciente.

Con esto concluye nuestra demostración de cómo reducir un registro de transacciones de la base de datos de Microsoft SQL Server.

Gracias por su tiempo.