![](assets/img/Database_Maintenance.png)
by Leonardo Lizano V.2017.05.12
La recopilación de datos es fundamental para que una empresa o institución mantenga sus relaciones. Por este motivo se le brinda una gran importancia al mantenimiento de la base de datos y también al constante crecimiento de la misma..
El mantenimiento para una base de datos siempre es muy importante ya que nos permite mantener nuestra Base de Datos en un nivel óptimo, estas operaciones de mantenimiento las podemos ejecutar mensualmente. Sin embargo, podemos variar esta frecuencia de acuerdo con el entorno y con la actividad que recibe cada base de datos.
Paso 1: Calcular peso de Base de Datos
SELECT DB_NAME(database_id) as [DatabaseName], Name as [Logical_Name],
Physical_Name as [Physical_Name], (size*8)/1024 as [SizeMB], ((size*8)/1024)/1024 as [SizeGB]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'NOMBREBASE'
GO
Paso 2: Calcular total de la base de datos sumados (MDF y LDF)
SELECT (sum(size)/1024)/1024*8 as [SizeGB]
FROM sys.master_files
WHERE DB_NAME(database_id)= 'NOMBREBASE'
GO
Paso 3: Primera compactación de la base de datos
DBCC SHRINKDATABASE ('NOMBREBASE')
USE 'NOMBREBASE';
GO
ALTER DATABASE 'NOMBREBASE' SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE ('NOMBREBASE'_LOG, 1);
GO
Paso 4: Desfragmentación al rescate, para evitar el deterioro del rendimiento en nuestro servidor, deberemos mantener nuestros índices en un estado de fragmentación óptimo. Lo podremos lograr sencillamente siguiendo estos pasos. Primer paso: detectar fragmentación en los índices de tu base de datos. Para ello, nos basaremos en la vista de sistema sys.dm_db_index_physical_stats, que encapsularemos en el siguiente Query
SELECT DB_NAME(database_id) AS DatabaseName, database_id, OBJECT_NAME(ips.object_id) AS TableName,
ips.object_id,i.name AS IndexName, i.index_id, p.rows,ips.partition_number, index_type_desc,
alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count,
avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count,
ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes,
avg_record_size_in_bytes, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') ips INNER JOIN
sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id INNER JOIN
sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE avg_fragmentation_in_percent > 10.0 AND ips.index_id > 0 AND page_count > 1000
ORDER BY avg_fragmentation_in_percent DESC
Paso 5: Ejecutar un script para desfragmentar los índices con problemas. El script determina si hay que hacer un Reorganize o un Rebuild para cada índice
USE 'NOMBREBASE'
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally SELECT tables and indexes FROM the sys.dm_db_index_physical_stats function and convert object and index IDs to names.
SELECT object_id AS [objected], index_id AS [indexid], partition_number AS [partitionnum], avg_fragmentation_in_percent AS [frag]
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 1000;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
Paso 6: Mantenimiento a una base de datos
Este comando examina la asignación y la integridad estructural de todos los objetos que se encuentran en la base de datos especificada, cuando acabe de ejecutarse nos mostrará un mensaje similar al siguiente cuando se ejecuta correctamente.
DBCC CHECKDB ('NOMBREBASE')
1- Poner la base de datos para acceso de un único usuario:
ALTER DATABASE 'NOMBREBASE' SET SINGLE_USER;
2- Luego ejecutar el siguiente Query de chequeo de la base de datos:
DBCC CHECKDB ('NOMBREBASE', REPAIR_REBUILD);
3- Por último, poner nuevamente la base de datos para acceso multiusuario:
ALTER DATABASE 'NOMBREBASE' SET MULTI_USER;
Paso 7: Reconstrucción de índices manualmente eligiendo las tablas de la base de datos
7.1- Se despliegan las tablas y se eligen a las que se les va a reconstruir los índices manualmente
7.2- En la ventana que muestra los índices y su porcentaje de fragmentación, se presiona [OK].
Paso 8: Compactación de la base de datos final
DBCC SHRINKDATABASE ('NOMBREBASE')
USE 'NOMBREBASE';
GO
ALTER DATABASE 'NOMBREBASE' SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE ('NOMBREBASE'_LOG, 1);
GO
ALTER DATABASE 'NOMBREBASE' SET RECOVERY FULL;
GO