Nota:
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
Versión del producto original: SQL Server
Número de KB original: 315512
Resumen
La configuración de crecimiento automático y de reducción automática controla cómo SQL Server aumenta automáticamente (crecimiento automático) o reduce (autohrink) el tamaño de los archivos de registro y datos de la base de datos. La configuración predeterminada es adecuada en muchos sistemas SQL Server, pero algunos entornos requieren ajustarla. En este artículo se explica cómo esta configuración afecta al rendimiento y proporciona procedimientos recomendados para ayudarle a evitar transacciones lentas, fragmentación de archivos y uso de espacio en disco no planeado.
Este artículo es para los administradores de bases de datos (DBA) que configuran el comportamiento de crecimiento y reducción de archivos de las bases de datos en SQL Server.
¿Cómo se configuran los ajustes de crecimiento automático y reducción automática?
Configure o modifique la configuración de crecimiento automático y reducción automática mediante uno de los métodos siguientes:
Una
ALTER DATABASEsentencia:- Use Opciones de Archivo y grupo de archivos para modificar la configuración de crecimiento automático (
FILEGROWTH). - Utilice las opciones SET para configurar la
AUTO_SHRINKconfiguración.
- Use Opciones de Archivo y grupo de archivos para modificar la configuración de crecimiento automático (
Nota
Para obtener más información sobre cómo establecer esta configuración en el nivel de archivo de base de datos, vea Agregar archivos de datos o de registro a una base de datos.
También puede configurar la opción de crecimiento automático al crear una base de datos.
Para ver la configuración actual, ejecute el siguiente comando de Transact-SQL:
sp_helpdb [ [ @dbname= ] 'name' ]También puede consultar la vista de catálogo sys.database_files de la base de datos actual o la vista de catálogo sys.master_files para todas las bases de datos de la instancia. Estas vistas incluyen el incremento de crecimiento, si el crecimiento es un porcentaje y el tamaño máximo de archivo. Para comprobar si
AUTO_SHRINKestá activado, consulte lais_auto_shrink_oncolumna en sys.databases.Tenga en cuenta que la configuración de crecimiento automático es por archivo. Por lo tanto, debe establecerlos en al menos dos lugares para cada base de datos (uno para el archivo de datos principal y otro para el archivo de registro principal). Si tiene varios archivos de datos y de registro, debe establecer las opciones en cada archivo. En función de su entorno, es posible que termine con una configuración diferente para cada archivo de base de datos.
¿Cuáles son los valores predeterminados de autogrow?
En SQL Server 2016 (13.x) y versiones posteriores, el valor predeterminado FILEGROWTH es 64 MB para los archivos de datos y de registro. Las versiones anteriores usan valores predeterminados más pequeños o basados en porcentajes. Para ver el valor predeterminado para cada versión, vea Opciones de archivo y grupo de archivos ALTER DATABASE (Transact-SQL).
¿Qué debo tener en cuenta antes de activar AUTO_SHRINK?
AUTO_SHRINK es una opción de base de datos en SQL Server. Al habilitar esta opción para una base de datos, una tarea en segundo plano puede contraer la base de datos. Esta tarea en segundo plano evalúa todas las bases de datos que cumplen los criterios para reducir y, a continuación, reduce los archivos de datos o de registro.
Evalúe cuidadosamente la posibilidad de establecer esta opción para las bases de datos de una instancia de SQL Server. Las operaciones de crecimiento y reducción frecuentes pueden provocar varios problemas de rendimiento:
Si varias bases de datos se someten a operaciones frecuentes de reducción y crecimiento, se puede producir la fragmentación del sistema de archivos. La fragmentación puede afectar gravemente al rendimiento. Este impacto se produce tanto si usa la configuración automática como si aumenta y reduce manualmente el tamaño de los archivos con frecuencia.
Después de que
AUTO_SHRINKreduzca correctamente los datos o el archivo de registro, una operación DML o DDL posterior puede ralentizarse significativamente si se requiere espacio y los archivos deben crecer.La
AUTO_SHRINKtarea en segundo plano puede usar recursos significativos cuando muchas bases de datos necesitan reducir su tamaño.La
AUTO_SHRINKtarea en segundo plano tiene que adquirir bloqueos y otros objetos de sincronización que pueden entrar en conflicto con la actividad regular de la aplicación.
En su lugar, establezca las bases de datos en el tamaño necesario y amplíelas previamente. Deje el espacio sin usar en los archivos de base de datos si cree que la aplicación volverá a necesitarlo. Para obtener más información sobre la reducción de archivos, consulte Reducción de una base de datos y DBCC SHRINKFILE.
¿Qué debo tener en cuenta antes de confiar en autogrow?
Autogrow puede afectar a la duración de la transacción, otras sesiones y el número de archivos de registro virtuales (VLFs) en el registro de transacciones:
Si ejecuta una transacción que requiere más espacio de registro de lo que está disponible y el crecimiento automático está activado para el registro de transacciones de esa base de datos, el tiempo que tarda la transacción en completarse incluye el tiempo que tarda el registro de transacciones en crecer por la cantidad configurada. Si el incremento del crecimiento es grande u otro factor hace que el crecimiento tarde mucho tiempo, la consulta con la que se abre la transacción podría producir un error debido a un error de tiempo de espera. El mismo problema puede deberse a un autocrecimiento de la parte de datos de la base de datos.
Si ejecuta una transacción grande que requiere que crezca el registro, otras transacciones que requieren una escritura en el registro de transacciones también tienen que esperar hasta que se complete la operación de crecimiento.
Si tiene muchos crecimientos de archivos en los archivos de registro, es posible que tenga un número excesivo de archivos de registro virtuales (VLFs). Muchos VLFs pueden degradar el inicio de la base de datos, las operaciones en línea y otras funciones dependientes del registro de transacciones. Para comprobar el número de VLF, consulte la función de administración dinámica sys.dm_db_log_info.
Si aumenta la base de datos por pequeños incrementos, o si la aumenta y la reduce, puede acabar provocando fragmentación del disco. La fragmentación del disco puede causar problemas de rendimiento en algunas circunstancias. Los pequeños incrementos también pueden reducir el rendimiento del sistema.
Nota
Si combina las opciones de crecimiento automático y de reducción automática, puede crear una sobrecarga innecesaria. Asegúrese de que los umbrales que desencadenan las operaciones de crecimiento y reducción no provoquen cambios frecuentes de aumento y disminución del tamaño. Por ejemplo, puede ejecutar una transacción que haga que el registro de transacciones aumente en 100 MB en el momento en que se confirma. Algún tiempo después de eso, autoshrink comienza y reduce el registro de transacciones en 100 MB. A continuación, ejecutas la misma transacción y eso hace que el registro de transacciones crezca de nuevo en 100 MB. En ese ejemplo, va a crear una sobrecarga innecesaria y potencialmente puede crear fragmentación del archivo de registro, lo cual en ambos casos puede afectar negativamente al rendimiento.
¿Cómo afecta la inicialización instantánea de archivos al crecimiento automático?
La inicialización instantánea de archivos (IFI) acelera las operaciones de crecimiento automático de archivos de datos porque SQL Server no rellena con ceros el nuevo espacio. En SQL Server 2022 (16.x) y versiones posteriores, los eventos de crecimiento automático del registro de transacciones de hasta 64 MB también pueden beneficiarse de IFI. Sin IFI, el crecimiento automático tarda más tiempo a medida que el incremento de crecimiento aumenta. Para conocer los requisitos y las limitaciones, consulte Inicialización instantánea de archivos de base de datos.
¿Cuáles son los procedimientos recomendados para autogrow y autoshrink?
Para un sistema de producción administrado, considere la posibilidad de que autogrow sea simplemente una contingencia para un crecimiento inesperado. No administre los datos ni el crecimiento del registro diariamente mediante el autocrecimiento.
Utilice alertas o programas de supervisión para supervisar los tamaños de archivo y ampliar los archivos de forma proactiva. Este enfoque le ayuda a evitar la fragmentación y le permite cambiar estas actividades de mantenimiento a fuera de las horas punta.
Haga que un administrador de bases de datos (DBA) entrenado evalúe cuidadosamente el crecimiento automático y la reducción automática. No las dejes sin administrar.
Haga que el incremento de crecimiento automático sea lo suficientemente grande como para evitar las penalizaciones de rendimiento descritas anteriormente en este artículo. El valor exacto que se va a usar en el parámetro de configuración y la elección entre un crecimiento porcentual y un crecimiento de tamaño específico en MB dependen de muchos factores del entorno. Una regla general que puede usar para realizar pruebas es establecer la configuración de crecimiento automático en aproximadamente un octavo del tamaño del archivo. Para obtener recomendaciones sobre el crecimiento del registro de transacciones, consulte Administrar el tamaño del archivo de registro de transacciones.
Active la opción
MAXSIZEde cada archivo para evitar que un archivo crezca hasta un punto en el que use todo el espacio en disco disponible.Mantenga el tamaño de las transacciones lo más pequeño posible para evitar el crecimiento de los archivos no planeados.
¿Por qué todavía necesito supervisar el espacio en disco cuando el crecimiento automático está activado?
La configuración de crecimiento automático no puede aumentar el tamaño de la base de datos más allá de los límites del espacio en disco disponible en las unidades para las que se definen los archivos. Por lo tanto, si confía en la funcionalidad de crecimiento automático para ajustar el tamaño de las bases de datos, debe comprobar de forma independiente el espacio disponible en disco duro. La configuración de crecimiento automático también está limitada por el
MAXSIZEparámetro que seleccione para cada archivo. Para reducir la posibilidad de quedarse sin espacio, puede supervisar el contador SQL Server: Databases Object: Data File(s) Size (KB) y configurar una alerta cuando la base de datos alcance un tamaño determinado.El crecimiento no planeado de los archivos de datos o de registro puede ocupar espacio que otras aplicaciones esperan que esté disponible y puede hacer que esas otras aplicaciones experimenten problemas.
El incremento de crecimiento del registro de transacciones debe ser lo suficientemente grande como para mantenerse al día de las necesidades de las operaciones de transacción. Incluso con el crecimiento automático activado, puede recibir un mensaje que indica que el registro de transacciones está lleno si no puede crecer lo suficientemente rápido como para satisfacer las necesidades de la consulta. Para obtener más información, consulte Solución de problemas de un registro de transacciones completo (SQL Server error 9002).
¿Con qué frecuencia SQL Server comprueba las bases de datos para la reducción automática?
SQL Server no prueba constantemente las bases de datos que han alcanzado el umbral configurado para la reducción automática. En su lugar, examina las bases de datos disponibles y busca la primera que está configurada para autoshrink. Comprueba la base de datos y la reduce si es necesario. A continuación, espera varios minutos antes de comprobar la siguiente base de datos que está configurada para autoshrink.
En otras palabras, SQL Server no comprueba todas las bases de datos a la vez y las contrae todas a la vez. Funciona a través de las bases de datos de forma round robin para escalonar la carga durante un período de tiempo. Por lo tanto, en función del número de bases de datos que haya configurado para la reducción automática en una instancia determinada de SQL Server, la base de datos puede tardar varias horas desde el momento en que alcanza el umbral hasta que realmente se reduce.