Intro
Esta es otra de mis aventuras como DBA de SQL Server.
El reto ahora es reducir el tamaño de una base de datos de SQL Server. El servidor es SQL Server 2008 R2. Lo bueno es que esta base de datos es para los desarrolladores y tengo permisos de romper cosas, borrar bitácoras, alterar valores, etc. ¡A darle pues! ¿De qué tamaño es la BD?
Este es el primer paso. Averiguar qué tanto espacio está ocupando la base
de datos. De antemano sé que el archivo de respaldo pesa alrededor de 800 GB.
¿Pero cuánto usa en realidad en el disco duro?
Pinal Dave al rescate:
SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB, ((size*8)/1024)/1024 SizeGB FROM sys.master_files WHERE DB_NAME(database_id) = 'MI_DB_GIGANTE'; GO
El resultado:
DatabaseName Logical_Name Physical_Name SizeMB SizeGB ============================================================================== MI_DB_GIGANTE db_Data C:\MI_DB_GIGANTE\Data.mdf 238000 232 MI_DB_GIGANTE db_Log C:\MI_DB_GIGANTE\Log.ldf 100679 98 MI_DB_GIGANTE db_Index C:\MI_DB_GIGANTE\Index.mdf 489500 478 MI_DB_GIGANTE db_Data03 C:\MI_DB_GIGANTE\Data03.mdf 75000 73 MI_DB_GIGANTE db_Data04 C:\MI_DB_GIGANTE\Data04.mdf 75000 73
Y para calcular el gran total haremos un query a sys.master_files:
SELECT (SUM(size)/1024)/1024*8 FROM sys.master_files WHERE DB_NAME(database_id) = 'MI_DB_GIGANTE' GO
Y el resultado en Gigabytes:
952
Nota: sys.master_files.size es el tamaño del archivo en bloques de 8KB.
El resultado original era 125206936 bloques de 8KB cada uno. ¡Casi un
Terabyte!
Ahora ¿cómo le hago para reducir el tamaño?
Recovery model
El Recovery Model de SQL Server afecta la manera en que se hacen respaldos y cómo se restauran. SQL Server puede registrar todas y cada una de las operaciones de la base de datos en una bitácora. La bitácora permite restaurar una base de datos en un punto exacto en el pasado, por ejemplo: ayer a las 3:45 pm. El modo de recuperación controla la generación de las bitácoras.
Existen tres modos de recuperación:
-
Simple. No guarda nada en las bitácoras.
-
Full. Guarda todas las operaciones en las bitácoras. El modelo más seguro.
-
Bulk logged. Guarda las operaciones en masa o volumen. Puede perder datos si la bitácora se daña, pero usa menos espacio en disco.
Tengo la ventaja de que esta base de datos es para los desarrolladores, así que la decisión es fácil: podemos usar el modo Simple.
ALTER DATABASE [MI_DB_GIGANTE] SET RECOVERY SIMPLE WITH NO_WAIT; ALTER DATABASE [MI_DB_GIGANTE] SET PAGE_VERIFY NONE WITH NO_WAIT;
Con esto nos aseguramos que las bitácoras no crezcan. ¿Qué sigue?
Encoger las bitácoras
CUIDADO: Encoger las bitácoras hará que pierdas información. Yo tengo la ventaja de tener una base de datos de prueba. No tomes esto como una recetita sin entender lo que estás haciendo. Para dejar claro, no sigas leyendo este post si antes no has leído los siguientes sitios:
-
SQL SERVER – SHRINKFILE and TRUNCATE Log File in SQL Server 2008 de Pinal Dave
-
Why you should not shrink your data files de Paul S. Randal
¿Ya los leíste? Sigamos.
Anteriormente ya había sacado el tamaño del archivo de LOG. Era de 98
Gigabytes. Con DBCC SHRINKFILE me podré deshacer de los logs.
DBCC SHRINKFILE (Data_Log)
¿Sirvió de algo? Vamos a averiguarlo ejecutando la primera consulta de este
post, pero ahora sobre sys.master_files:
DatabaseName Logical_Name Physical_Name SizeMB SizeGB ============================================================================== MI_DB_GIGANTE db_Data C:\MI_DB_GIGANTE\Data.mdf 238000 232 MI_DB_GIGANTE db_Log C:\MI_DB_GIGANTE\Log.ldf 1 0 MI_DB_GIGANTE db_Index C:\MI_DB_GIGANTE\Index.mdf 489500 478 MI_DB_GIGANTE db_Data03 C:\MI_DB_GIGANTE\Data03.mdf 75000 73 MI_DB_GIGANTE db_Data04 C:\MI_DB_GIGANTE\Data04.mdf 75000 73
¡Qué bien! ¡Se liberaron 98 Gigabytes!
Encogiendo los datos
Usé DBCC SHRINKDATABASE, pero no liberó demasiado espacio.
DBCC SHRINKDATABASE (MI_DB_GIGANTE, 10); GO
La documentación dice que para que regrese el espacio usado al sistema
operativo hay que usar el parámetro TRUNCATEONLY.
DBCC SHRINKDATABASE (MI_DB_GIGANTE, TRUNCATEONLY); GO
Pero tampoco vi ninguna mejora significativa.
DatabaseName Logical_Name Physical_Name SizeMB SizeGB ============================================================================== MI_DB_GIGANTE db_Data C:\MI_DB_GIGANTE\Data.mdf 237970 232 MI_DB_GIGANTE db_Log C:\MI_DB_GIGANTE\Log.ldf 1 0 MI_DB_GIGANTE db_Index C:\MI_DB_GIGANTE\Index.mdf 489500 478 MI_DB_GIGANTE db_Data03 C:\MI_DB_GIGANTE\Data03.mdf 75000 73 MI_DB_GIGANTE db_Data04 C:\MI_DB_GIGANTE\Data04.mdf 75000 73
Encogiendo los archivos de datos
Estaba monitoreando la misma base de datos cuando vi que alguien más estaba
corriendo DBCC sobre uno de los archivos de datos. Le pregunté acerca de
eso y como resultado intenté de nuevo en mi base de datos.
Elegí db_Data03 como conejillo de indias.
DBCC SHRINKFILE (db_Data03)
El comando tardó dos minutos en completar y devolvió las siguientes estadísticas:
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
27 4 9600000 9600000 8090720 8090720
En la documentación de DBCC explican qué significa cada uno de esos campos. Las que me interesan son:
-
CurrentSize: el número de páginas de
8 KBque el archivo ocupa actualmente. -
MinimumSize: el número de páginas de
8 KBque el archivo podría ocupar, como mínimo. -
UsedPages: el número de páginas de
8 KBque utiliza actualmente el archivo. -
EstimatedPages: el número de páginas de
8 KBque el Motor de base de datos estima que puede reducir del archivo.
Por lo que veo no voy a tener suerte con este archivo. Voy a probar con db_Data04 y db_Data:
DBCC SHRINKFILE (db_Data04) DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages 18 5 9600000 9600000 8093648 8093648 DBCC SHRINKFILE (db_Data) DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages 18 1 30226208 128 30224168 30224168
Se me terminó el tiempo :( Ojalá tenga tiempo para terminarlo y ofrecer alguna solución útil.
FIN