Reducción de base de dato SQL Server – Cómo y cuándo programar y realizar la reducción de archivos de la base de datos

De tiempo en tiempo, los DBAs se encuentran en una situación donde una base de datos SQL Server se vuelve muy grande para su ambiente de producción y necesita ser reducida en tamaño para liberar espacio y asignarlo de vuelta al sistema.

Antes de reducir una base de datos SQL Server o archivos de bases de datos, es muy importante entender cómo funciona el proceso y cuáles son las consecuencias inmediatas del proceso de reducción.

Reducir una base de datos SQL Server es un proceso en el cual el espacio sin usar que estuvo previamente en uso en algún punto de tiempo es removido de la base de datos. SQL Server realiza este proceso moviendo páginas de datos desde el final de la base de datos al principio de ella (como si fuera un libro), llenando el espacio libre en las páginas del principio y liberando páginas cercanas al final del archivo de datos. Con esto, las páginas de datos al final del archivo de datos (que ahora están sin usarse) pueden ser reasignadas de vuelta al sistema, liberando el espacio previamente reservado como resultado. El proceso de reducción está siempre moviéndose en esa dirección específica, desde el final del archivo hacia el principio.

Cuándo realizar una reducción de la base de datos SQL Server

Muchos expertos en SQL Server están en contra de la reducción de bases de datos SQL Server, especialmente como una acción frecuente y pre programada de mantenimiento. Sin embargo, hay algunas situaciones críticas cuando es considerada como la única solución válida aplicable:

• Después de un gran trabajo de archivado.
• Después de eliminar una gran cantidad de tablas.
• Después de cambios substanciales en los tipos de datos de una tabla.

En general, la reducción de bases de datos SQL Server es más efectivamente usada para reducir el tamaño del archivo de la base de datos después de operaciones que han resultado con una gran cantidad de espacio sin usar, como las operaciones de tabla DROP y TRUNCATE.

Cómo verificar el tamaño actual de los archivos de la base de datos

Para mostrar los datos y la información de los archivos del registro para una base de datos SQl Server y verificar la información de espacio en el disco:

• Cree un reporte estándar para la base de datos en SQL Server Management Studio o
• Realice una operación SELECT en sys.database_files.

• Para verificar los datos y la información de espacio en el registro para una base de datos desde SSMS, realice los siguientes pasos:

  1. Inicie SSMS y conéctese al motor de la base de datos de SQL Server.
  2. En el panel Object Explorer, localice la base de datos en cuestión y haga clic derecho para abrir el menú contextual.
  3. Elija Reports / Standard Reports / Disk Usage.

     

• Como una alternativa a obtener la información del tamaño de los datos desde SSMS, la información puede ser adquirida ejecutando el procedimiento almacenado ‘sp_spaceused’ contra la base de datos.

  

  Correr el procedimiento almacenado es una opción más conveniente, dado que permite correr la verificación contra una tabla específica solamente.

  USE ApexSQL_Backup_Shrink;
  GO
  
  EXEC sp_spaceused N'Table_1';
  GO
  

  Aquí está un ejemplo de los resultados después de ejecutar el procedimiento almacenado especificado.

  

• La tercera opción, como se mencionó previamente, es ejecutar la operación SELECT contra sys.database_files.

  USE ApexSQL_Backup_Shrink;
  GO
  
  SELECT file_id
  	,NAME
  	,type_desc
  	,physical_name
  	,size
  	,max_size
  FROM sys.database_files;
  GO
  

  Aquí está un ejemplo de los resultados cuando se ejecuta la sentencia SELECT especificada.

  

¿Qué considerar antes de realizar una reducción de una base de datos SQL Server?

Antes de decidirse a reducir la base de datos, es importante estar conscientes de todas las limitaciones y restricciones del proceso de reducción de la base de datos, así como el posible impacto negativo en el desempeño de la base de datos y SQL Server.

Limitaciones:

• No es posible realizar una reducción de una base de datos SQL Server mientras un proceso de respaldo de la base de datos está ejecutándose y viceversa.
• Una base de datos no puede ser reducida indefinidamente. Cuando la base de datos fue creada inicialmente, el tamaño mínimo de la base de datos ha sido especificada y reducirla no la hace más pequeña que este valor. Incluso una base de datos completamente vacía no puede ser reducida por debajo de 1GB si el tamaño mínimo inicial ha sido establecido a 1GB.

Contras:

• Reducir una base de datos SQL Server ignora completamente la fragmentación lógica de los índices y resulta en una fragmentación masiva de todos los índices. Esto puede tener un impacto negativo en el desempeño de de las consultas, dado que tomará más tiempo ‘localizar’ los índices fragmentados. Realizar una reconstrucción inmediata de los índices puede resolver el problema, pero no completamente, dado que negará parcialmente el proceso de reducción y creará algo de espacio ‘sin usar’ de nuevo para desfragmentar apropiadamente los índices, lo cual, hasta cierto punto, niega los resultados de la reducción de la base de datos.
• Los archivos reducidos inevitablemente crecerán de nuevo, ya que la mayoría de bases de datos de producción tienen al menos ‘algo’ de actividad. Esto significa que el archivo de la base de datos será incrementado en tamaño, y este proceso toma tiempo y utiliza una cantidad nada despreciable de recursos, lo cual puede producir impactos en el desempeño en ambientes con trabajo intensivo.
• Realizar la reducción de múltiples bases de datos en múltiples ocasiones puede resultar en fragmentación del disco (fragmentación del sistema de archivos), lo cual puede causar problemas de desempeño.
• El proceso de asignación de páginas será escrito como una actividad en el archivo del registro de transacciones, lo cual puede resultar en un crecimiento masivo del archivo de registro de transacciones, especialmente en bases de datos en el modelo de recuperación completo.
• Reducciones subsecuentes y el crecimiento de los archivos del registro de transacciones ralentizará el inicio de la base de datos, la restauración y el tiempo de replicación.

Cómo reducir una base de datos usando T-SQL

1. Conéctese al motor de base de datos vía SSMS u otra herramienta de administración.
2. Ejecute DBCC SHRINKDATABASE contra base de datos en cuestión.

   Nota: para determinar el porcentaje de espacio libre máximo en los archivos después de que el proceso de reducción es finalizado, se requiere especificar el valor de %.

Por ejemplo, reducir la base de datos ApexSQL_Backup_Shrink con un máximo de 25% de espacio libre restante en los archivos:

DBCC SHRINKDATABASE (
		ApexSQL_Backup_Shrink
		,25
		);

Cómo reducir una base de datos SQL Server con SQL Server Management Studio

1. Inicie SSMS y conéctese al motor de base de datos de SQL Server.
2. En el panel Object Explorer localice la base de datos en cuestión y haga clic derecho para abrir el menú contextual.
3. Navegue a Tasks / Shrink / Database.
4. En el diálogo Shrink database, detalles acerca del tamaño de la base de datos serán provistos y una opción para elegir si los archivos serán reorganizados antes de que el espacio no usado sea liberado.

   Nota: si la opción Reorganize files before releasing unused space está seleccionada, será posible elegir el porcentaje máximo restante de espacio libre en archivos después de que el proceso de reducción sea finalizado. Cuando esta opción es seleccionada, el resultado será el mismo que cuando se ejecuta DBCC SHRINKDATABASE y se especifica una opción de porcentaje objetivo. Si esta opción no es seleccionada, el resultado será el mismo que cuando se ejecuta DBCC SHRINKDATABASE con la opción TRUNCATEONLY.

   

5. Haga clic en el botón OK para completar el proceso de reducción.

Traductor: Daniel Calbimonte