En este artículo, vamos a discutir la propiedad de base de datos de SQL Server Auto Shrink, que permite a SQL Server para reducir automáticamente los archivos de base de datos cuando el valor está configurado como True en la opción de base de datos. La operación de contracción automática se realiza mediante la característica de base de datos de contracción automática del servidor, que es el curso principal de este artículo.
Para evitar la actividad de contracción de los archivos de datos/registro cada vez que el tamaño del archivo supera el espacio libre en el archivo de datos, el DBA (administrador de la base de datos) debe hacer una copia de seguridad de los archivos de registro en un intervalo regular. Hacer una copia de seguridad de toda la base de datos no es una buena idea; hay que construir o configurar también el registro de transacciones. Los registros de transacciones aumentarán en número hasta ocupar todo el espacio disponible en el disco si no se hace una copia de seguridad de ellos. Si se hace una copia de seguridad de la base de datos, se libera el espacio libre para ser reutilizado de nuevo. El administrador de la base de datos debe programar una copia de seguridad del registro de transacciones para reducir el tamaño de los archivos de registro a un tamaño justo.
La función de reducción automática de SQL Server está desactivada por defecto en las bases de datos de instancia de SQL Server. En el escenario donde usted tiene un número de bases de datos relativamente pequeñas que crecen a un tamaño más grande, ya sea por la inserción de nuevas tuplas o por un gran número de espacios vacíos creados por la eliminación de varias tuplas, SQL Server Auto Shrink se convierte en bastante útil en esta situación. Además, no debe preocuparse por la fragmentación del tamaño de los archivos de la base de datos.
Mientras que la reducción de la base de datos, usted debe tener en cuenta las operaciones de reducción en ambos críticos, así como las bases de datos relativamente más grande como una parte del mantenimiento. Además, se debe evitar ejecutar la función de encogimiento manual; las consecuencias de hacer esto es que uno nunca estará al tanto de los problemas relacionados con la solicitud nueva o existente. Sin embargo, la reducción de los archivos de transacción es mejor que la reducción de los archivos de datos.
¿Cómo activar y desactivar la contracción automática de SQL Server para la base de datos?
Los usuarios pueden activar y desactivar la opción de contracción automática de la base de datos utilizando SSMS y T-SQL de ambas maneras.
Activar la contracción automática de la base de datos utilizando SSMS:
Los usuarios pueden activar o desactivar esta opción desde las propiedades de la base de datos, con una etiqueta de Auto Shrink. Aquí, True en el desplegable habilitará esta opción para su base de datos.
Habilitar el autoencogimiento de la base de datos mediante T-SQL:
Los usuarios pueden ejecutar las siguientes sentencias T-SQL para habilitar o deshabilitar el encogimiento de la base de datos. Hemos utilizado la base de datos AdventureWorks para la declaración T-SQL anterior.
|
1
2
3
4
5
6
|
–Habilitar Auto Shrink para la base de datos AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
–Desactivar la contracción automática para la base de datos AdventureWorks
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GO
|
Efectos de la contracción de la base de datos en el rendimiento de la consulta
Se pueden plantear efectos negativos en el rendimiento de la consulta si se activa la opción de contracción automática de la base de datos y la configuración de crecimiento automático conjuntamente para la base de datos. Podemos mantener un poco de espacio para los archivos de datos para crecer y también evitar algunos eventos frecuentes de auto-crecimiento mediante la configuración del tamaño de la base de datos a un valor óptimo o en su mayoría cada base de datos tienen algunos parámetros con las características de auto-crecimiento habilitado.
Para este tipo de bases de datos, las características de auto-encogimiento debe ser activado por nosotros cuando la base de datos es más pequeño, uno y no más de la operación CRUD, por lo que puede hacer que la contracción de los archivos de datos posible y recuperar el espacio libre que se aprovisionó a propósito para mantener lejos de nuestras bases de datos de eventos de auto-crecimiento. El espacio libre se liberará automáticamente en los archivos de datos y archivos de registro periódicamente en la prioridad secuencial round-robin por la función de Auto shrink si múltiples bases de datos han activado esta característica.
Para las bases de datos de gran tamaño, el auto-crecimiento, y después, el auto shrink se ejecutará que dará lugar a problemas de rendimiento que conducen a la fragmentación a nivel de sistema. Para resumir todo esto, para cualquier base de datos, no se debería activar el autoencogimiento por las siguientes razones:
- Ejecutar los algoritmos de autoencogimiento de SQL Server sin ningún propósito definitivamente va a despilfarrar los recursos en una cantidad masiva
- Ya sea que ejecute el autoencogimiento de SQL Server o el encogimiento manual, obviamente va a causar la fragmentación del índice y esto eventualmente ejecuta el encogimiento de los archivos de datos también
- Si el servidor también está forzando los límites del subsistema IO, ejecutar el encogimiento puede empujarlo, resultando en largas colas de disco y posiblemente tiempos de espera de IO, esto consumirá los recursos de IO y CPU del servidor en una cantidad masiva
- El rendimiento del sistema se retrasará por la fragmentación a nivel de disco del sistema de archivos, que es de nuevo el efecto directo de hacer crecer y encoger los archivos de datos a menudo
Para entender más acerca de los eventos de crecimiento y contracción en SQL Server, lea este artículo, Obtenga detalles de los eventos de crecimiento y contracción de la base de datos de SQL Server.
Para la base de datos crítica, la operación de contracción manual podría ser ejecutada en el nivel de archivo de base de datos por el experto. La actividad manual de reducción de archivos puede realizarse cuando se ejecuta la operación de eliminación y, a partir de entonces, se ha recuperado espacio. Debemos reconstruir los índices fragmentados tras la ejecución de la operación de reducción, ya que la operación de reducción puede provocar la fragmentación de los índices. El porcentaje de fragmentación de los índices puede ser comprobado por el usuario con el uso de sentencias T-SQL de DMV’s. Sin embargo, la reducción del archivo de registro debe hacerse manualmente como y cuando se requiere y no debe ser parte de la actividad de mantenimiento regular.
Para supervisar el uso del disco de forma regular, el informe de uso del disco podría ser analizado por el usuario antes de realizar la operación de reducción manual utilizando SSMS, que le da una visión de los datos y la información de espacio de registro para una base de datos en la pantalla. Si el usuario desea obtener un cuadro de mando para un cálculo de espacio reservado y espacio libre para los archivos de base de datos, a continuación, Informe de disco se convierte en muy útil. Sin embargo, el informe de disco habita la información utilizando DMV’s de SQL Server. El informe de disco está disponible en el siguiente directorio usando SSMS.
Base de datos >> Informes >> Informes estándar >> Uso de disco
Aquí, tenemos un Informe de disco para la base de datos. El usuario puede ver las estadísticas en tiempo real de los archivos de datos y de registro. Este informe incorpora información primaria de Espacio Total Reservado, Espacio Reservado de los Archivos de Datos, Espacio Reservado de los Registros de Transacciones y Espacio Reservado de OLTP en Memoria.
Contraer la base de datos automáticamente utiliza Trabajos de SQL Server
Estos scripts podrían ser realizados programando un trabajo por el usuario para ejecutar la operación de contracción de la base de datos con la actividad del programador. Como primer paso en el script, encontrar el espacio libre en el archivo de la base de datos y, a continuación, reducir ese archivo si los criterios definidos para el espacio libre se emparejan. El espacio libre debe ser calculado con la ayuda de SQL Server DMV’s.
Básicamente, la monitorización del archivo de la base de datos es realizada por el administrador de la base de datos, que a su vez, actúa sobre la base de datos para monitorizar el tamaño del archivo de la base de datos. Si el límite predefinido es cruzado por cualquier archivo, entonces necesitan realizar tales pasos. Así que esa actividad puede ser automatizada utilizando el trabajo de SQL Server para realizar sobre una base diaria, semanal o mensual en un momento predefinido.
La mejor práctica es programar este trabajo en el archivo de registro de base de datos sólo y supervisar el espacio libre del archivo de datos manualmente. Porque también puede afectar al rendimiento de las consultas. SQL Server DMV devolverá el archivo de datos y el archivo de registro en el mismo conjunto de resultados, por lo que es necesario bifurcar el tipo de archivo en la sentencia T-SQL de SQL Server DMV. Antes de reducir el archivo, el usuario puede aplicar la lógica de consulta sobre el espacio total y el espacio libre del archivo de registro. Si los criterios de cálculo coinciden con la propiedad del archivo, entonces el archivo se reducirá con el tamaño de archivo objetivo.
Comprobar el espacio libre para los archivos de la base de datos:
Si dichos criterios coinciden con los archivos de la base de datos, por ejemplo, si el espacio libre es mayor que (n) MB/GB, si el n(%) de espacio libre se compara con el espacio total, y muchos más. Cuando el espacio libre no está disponible para el archivo de registro y el tamaño del archivo está cerca del valor del parámetro de tamaño máximo del archivo, entonces el administrador de la base de datos necesita buscar en el registro de transacciones.
Reducir archivo de base de datos:
|
1
|
DBCC SHRINKFILE(nombre_archivo, 5120);
|
Aquí, 5120 es un tamaño de archivo objetivo en MB. Por lo tanto, el archivo se convertirá en un tamaño de 5120 MB. Configure la consulta T-SQL con los criterios requeridos en el paso de trabajo de SQL Server y prográmelo en horas de encendido y apagado.
La opción de reducción automática de SQL Server no se puede habilitar para todas las bases de datos. Específicamente, ayuda para la base de datos más pequeña, que realiza menos operación CRUD comparativamente.
Conclusión
En este artículo, discutimos la propiedad de base de datos Auto Shrink en SQL Server para reducir los datos y archivos de registro y eliminar el espacio no utilizado. Reducir una base de datos es una operación costosa y debe utilizarse con cuidado.
- Autor
- Postes Recientes
Ver todos los comentarios de Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Cómo automatizar el particionamiento de tablas en SQL Server – 7 de julio de 2020
- Configuración de grupos de disponibilidad Always On de SQL Server en AWS EC2 – 6 de julio de 2020