Resumen

La configuración predeterminada de crecimiento automático y autorreducción funcionará para usted con ningún ajuste en muchos sistemas de SQL Server. Sin embargo, hay entornos donde no es necesario activar la configuración o donde tendrá que ajustar los parámetros de crecimiento automático y autorreducción . Este artículo proporciona información general que le guiarán al seleccionar la configuración para su entorno.

Más información

Aquí presentamos algunas consideraciones a tener en cuenta si decide ajustar los parámetros de crecimiento automático y autorreducción .

¿Cómo se puede cambiar la configuración?

  1. Puede configurar la configuración de crecimiento automático y autorreducción mediante uno de estos procedimientos:

    • Una instrucción ALTER DATABASE

    • SQL Server Management Studio

    • El procedimiento almacenado sp_dboption (en desuso en SQL Server 2005)

    Nota: Para obtener más información acerca de cómo configurar estos valores en SQL Server 2005, visite los siguientes sitios Web de Microsoft Developer Network (MSDN):

    Cómo: agregar datos o archivos de registro a una base de datos (SQL Server Management Studio)
    http://msdn2.microsoft.com/en-us/library/ms189253.aspx
    Propiedades de base de datos (página de archivos)
    http://msdn2.microsoft.com/en-us/library/ms180254.aspxTambié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' ]
  2. Tenga en cuenta que la configuración de crecimiento automático por archivo. Por lo tanto, deberá 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 o de registro, debe establecer las opciones de cada archivo. Dependiendo de su entorno, puede terminar con una configuración distinta para cada archivo de base de datos.

¿Cuáles son las implicaciones de rendimiento?

  • Si se ejecuta una transacción que requiere más espacio del registro que está disponible y ha activado la opción de crecimiento automático del registro de transacciones de esa base de datos, el tiempo que tarda la transacción finalice incluirá el tiempo que tarda el registro de transacciones crecimiento en la cantidad configurada. Si el incremento de crecimiento es grande o hay algún otro factor que hace que llevar mucho tiempo, la consulta en el que abrir la transacción puede fallar debido a un error de tiempo de espera. El mismo tipo de problema puede resultar de un crecimiento automático de la parte de datos de la base de datos. Para cambiar la configuración de crecimiento automático , consulte el tema "ALTER DATABASE" en libros en pantalla de SQL Server.

  • Si ejecuta una transacción grande que requiere el registro de crecimiento, otras transacciones que requieren una escritura en el registro de transacciones también tendrá que esperar hasta que finaliza la operación de crecimiento.

  • Si se combinan las opciones de crecimiento automático y autorreducción , podría crear innecesarios sobrecarga. Asegúrese de que no hará que los umbrales que desencadenan el crecimiento y reducir las operaciones frecuentes y Bajar los cambios de tamaño. Por ejemplo, puede ejecutar una transacción que hace que el registro de transacciones crezca por 100 MB en el momento en que confía. Algún tiempo después de que la reducción automática se inicia y reduce el registro de transacciones por 100 MB. A continuación, ejecutar la misma transacción y hace que el registro de transacciones crezcan de nuevo en 100 MB. En el ejemplo, se crea innecesarios sobrecarga y potencialmente creando fragmentación del archivo de registro, que puede afectar negativamente al rendimiento.

  • Fragmentación física de cambiar el tamaño de los archivos de datos o de registro puede tener un efecto grave en el rendimiento. Esto es cierto si utiliza la configuración automática, o si usted manualmente aumentar y hundir los archivos con frecuencia.

  • Si aumentan su base de datos a intervalos pequeños, o si crecer y luego reducirlo, puede acabar con la fragmentación de disco. Fragmentación del disco puede producir problemas de rendimiento en algunas circunstancias. Un escenario de incrementos de crecimiento pequeño también puede reducir el rendimiento del sistema.

  • En SQL Server 2005 o en versiones posteriores, puede habilitar la inicialización instantánea de archivos. La inicialización instantánea de archivos acelera las asignaciones de archivo sólo para archivos de datos. La inicialización instantánea de archivos no funciona para los archivos de registro.

  • Si tiene numerosas ampliaciones del archivo en los archivos de registro, puede tener un número excesivamente grande de archivos de registro virtuales (VLF). Esto puede conducir a problemas de rendimiento con operaciones en Inicio y en línea de base de datos, replicación, espejado y cambiar la captura de datos (CDC). Además, en ocasiones, esto puede causar problemas de rendimiento con las modificaciones de datos.

Mejores prácticas

  • Para un sistema de producción administrado, debe tener en cuenta el crecimiento automático para ser simplemente una contingencia para crecimientos inesperados. No administre el crecimiento de datos y de registro en el día a día con el crecimiento automático.

  • Puede utilizar alertas o programas de supervisión para supervisar los tamaños de archivo y crecimiento de archivos de forma proactiva. Esto ayuda a evitar la fragmentación y permite cambiar las horas de estas actividades de mantenimiento.

  • Reducción y crecimiento automático deben evaluarse detenidamente por un administrador entrenado de base de datos (DBA); no debe estar en no administrado.

  • El incremento de crecimiento automático debe ser suficientemente grande como para evitar penalizaciones de rendimiento al enumerados en la sección anterior. El valor exacto para la configuración y la elección entre un porcentaje de crecimiento y un crecimiento de tamaño específico MB depende de muchos factores en su entorno. Una regla general que se puede utilizar para las pruebas es establecer su configuración de crecimiento automático a aproximadamente 1/8 del tamaño del archivo.

  • Activar la configuración de < MAXSIZE > para cada archivo para prevenir que alcance el punto en que ocupe todo el espacio disponible en disco.

  • Mantener el tamaño de sus transacciones tan pequeño como sea posible para evitar el crecimiento del archivo no planeado.

¿Por qué es necesario preocuparse de espacio en disco si se controla automáticamente la configuración de tamaño

  • El valor de crecimiento automático no puede aumentar el tamaño de la base de datos más allá de los límites del espacio disponible en disco en las unidades que se definen los archivos. Por lo tanto, si depende de la funcionalidad de crecimiento automático para ajustar el tamaño de las bases de datos, todavía de forma independiente debe comprobar el espacio disponible en disco duro. El valor de crecimiento automático también está limitado por el parámetro MAXSIZE que seleccione para cada archivo. Para reducir la posibilidad de quedarse sin espacio, puede supervisar el contador del Monitor de rendimiento de SQL Server: bases de datos de objeto: tamaño de los archivos de datos (KB) y configurar una alerta para cuando la base de datos alcanza un tamaño determinado.

  • Crecimiento no planificado de los archivos de datos o de registro puede tomar el espacio que otras aplicaciones se espera que esté disponible y es posible que las otras aplicaciones a experimentar problemas.

  • El incremento de crecimiento del registro de transacciones debe ser lo suficientemente grande como para anticiparse a las necesidades de las unidades de la transacción. Incluso con el crecimiento automático activada, puede recibir un mensaje de que el registro de transacciones está lleno, si no puede crecer lo suficientemente rápido para satisfacer las necesidades de la consulta.

  • SQL Server no comprueba constantemente para bases de datos que han elegido el umbral configurado para la reducción automática. En su lugar, estudia las bases de datos disponibles y encuentra la primera que está configurado para la reducción automática. Comprueba que la base de datos y se reduce a esa base de datos si es necesario. A continuación, espera unos minutos antes de comprobar la siguiente base de datos está configurado para la reducción automática. En otras palabras, SQL Server no comprobar todas las bases de datos a la vez y comprimirlos todos a la vez. Funcionará a través de las bases de datos en turnos para escalonar la carga de salida durante un período de tiempo. Por lo tanto, dependiendo de cuántas bases de datos en una instancia concreta de SQL Server configurada para reducción automática, puede tardar varias horas desde el momento en que la base de datos llega al umbral hasta que realmente se reduce.


En la tabla siguiente proporciona más información sobre los productos o herramientas que comprueban automáticamente si esta condición en la instancia de SQL Server y las versiones del producto SQL Server contra la cual se evalúa la regla.

Software de regla

Título de la regla

Descripción de la regla

Versiones del producto con el que se evalúa la regla

Asesor de System Center

Configuración de crecimiento del archivo de base de datos de SQL Server es mayor que el espacio disponible en el disco actual

System Center Advisor determina si cualquier base de datos en la instancia de SQL Server tiene el crecimiento automático configurado para los archivos de base de datos. Si el incremento de crecimiento resultante es mayor que el espacio disponible en el volumen, asesor del centro de sistema genera una advertencia para esta base de datos de SQL Server. Si aparece esta advertencia de System Center Advisor, revise la configuración de los archivos afectados el crecimiento automático y tomar medidas correctivas. Para resolver esta situación mediante uno de los métodos siguientes:

  • Reducir el incremento de crecimiento tal que el crecimiento siguiente puede suceder en este volumen.

  • Agregar más espacio a este volumen.

  • Mover este archivo a otro volumen con espacio disponible para su crecimiento.

SQL Server 2008
SQL Server 2008 R2




Referencias

Para obtener más información acerca de cómo aumentar y hundir sus archivos de registro y de base de datos, haga clic en los números de artículo siguientes para verlos en Microsoft Knowledge Base:

256650 cómo reducir el registro de transacciones de SQL Server 7.0

272318 reducir el registro de transacciones en SQL Server 2000 con DBCC SHRINKFILE

317375 un registro de transacciones crece inesperadamente o se convierte en completo en un equipo que está ejecutando SQL Server

Error de 247751 : plan de mantenimiento de base de datos no reduce la base de datos

305635 el tiempo de espera cuando se expande automáticamente una base de datos

949523 la latencia de la duplicación transaccional es alta en SQL Server 2005 cuando el valor de la propiedad "Tamaño inicial" y el valor de la propiedad de crecimiento automático son pequeños

Para obtener más información acerca de la inicialización del archivo de base de datos, visite el siguiente sitio Web de Microsoft Developer Network (MSDN):

Inicialización del archivo de base de datos
Para obtener más información acerca de cómo habilitar la inicialización instantánea de archivos, visite el siguiente sitio Web de Microsoft:

¿Cómo y por qué se debe habilitar la inicialización instantánea de archivosPara obtener más información acerca de la arquitectura física del registro de transacciones, visite el siguiente sitio Web de MSDN:

Arquitectura física del registro de transacciones
SQL Server libros en pantalla; temas: "Transacción arquitectura física del registro"; "Reducir el registro de transacciones"

¿Necesita más ayuda?

Ampliar sus conocimientos

Explorar los cursos >

Obtener nuevas características primero

Unirse a Microsoft Insider >

¿Le ha sido útil esta información?

¿Cuál es tu grado de satisfacción con la calidad del lenguaje?
¿Qué ha afectado a tu experiencia?

¡Gracias por sus comentarios!

×