Hola chic@s, tengo un problema con un procedimiento de mantenimiento de BD que quiero hacer:
Lo que quiero conseguir es que el procedimiento recorra una BD y, dependiendo el número de fragmentación que tenga cada índice, lo reorganice o lo reconstruya, teniendo en cuenta también que la tabla tiene que ser de tipo 'BASE TABLE'
Aquí les dejo lo que tengo hecho hasta ahora, que parece estar bien pero no me ejecuta el alter index, si alguien sabe donde esta el fallo o sabe cómo hacer este procedimiento por favor, que me ayude.
*-------------------------------------------------*--------------------------------------------------*
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
use T2O_PPP_Stats_PROD
go
DECLARE @Funciona int /*Variable para controlar si la transacción funciona o no*/
DECLARE @fragmentacion decimal /*Variable que contendrá la cantidad de fragmentación de los índices*/
DECLARE @nombretabla sysname, @indice sysname /*VariableS que contendráN el nombre de la tabla y del indice*/
/*Declaramos una variable CURSOR que contenga todos los nombres de las tablas, los indices, con su fragmentacion, de la BD*/
DECLARE desfragmenta CURSOR FOR
SELECT OBJECT_NAME(dt.object_id),si.name, dt.avg_fragmentation_in_percent
FROM
(SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(''), NULL, NULL, NULL, 'DETAILED')
WHERE index_id <> 0) as dt /*Con index_id <> 0 evitamos traer información de tablas que no tienen índices (Heaps)*/
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
INNER JOIN INFORMATION_SCHEMA.TABLES
ON OBJECT_NAME(dt.object_id)=TABLE_NAME where table_type='BASE TABLE'
open desfragmenta /*Abrimos el cursor*/
BEGIN TRANSACTION Reorganizar /*Comenzamos una transaccion, llamada Reorganiza, como medida de seguridad.
Es opcional*/
BEGIN TRY
set @Funciona=0 /*Inicializamos Funciona a 0*/
fetch next from desfragmenta into @nombretabla, @indice, @fragmentacion /*Cogemos el 1ºvalor del cursor y
lo metemos en las variables que corresponden*/
while @@FETCH_STATUS=0 /*Mientras el estado del cursor sea 0, no haya llegado al fin*/
BEGIN
BEGIN TRY
IF (@fragmentacion>1)
BEGIN
IF (@fragmentacion<=33)
BEGIN
print 'Se reorganizará el Índice: ' + @indice + ', en la Tabla: ' + @nombretabla
EXEC ('ALTER INDEX ' + @indice + ' ON ' + @nombretabla + ' REORGANIZE')
print 'Se ha reorganizado la tabla ' + @nombretabla + '; Fragmentacion:'
print @Fragmentacion
END
ELSE
BEGIN
print 'Se recontruirá el Índice: ' + @indice + ', en la Tabla: ' + @nombretabla
EXEC ('ALTER INDEX ' + @indice + ' ON ' + @nombretabla + ' REBUILD');
print 'Se ha reconstruido la tabla ' + @nombretabla + '; Fragmentacion:'
print @Fragmentacion
END
END
END TRY
BEGIN CATCH /*Si falla algo en el TRY entra en el CATCH, sino sigue de largo*/
set @Funciona=1
print 'Error en la tabla '+@nombretabla + ' en la condición IF'
END CATCH
print @nombretabla
fetch next from desfragmenta into @nombretabla, @indice, @fragmentacion /*Le damos el siguiente valor del
cursor a nombretabla*/
END
END TRY
BEGIN CATCH /*Si falla algo en el TRY entra en el CATCH, sino sigue de largo*/
set @Funciona=1
print 'Error en el bucle'
END CATCH
print 'Funciona contiene el valor '
print @Funciona
if @Funciona=1
BEGIN
ROLLBACK TRANSACTION Reorganizar /*Dejamos la BD como estaba al principio*/
print 'No se ha modificado ningún dato de la BD'
END
else
BEGIN
COMMIT TRANSACTION Reorganizar /*Actualizamos la BD con los cambios realizados*/
print 'Se han modificado todos los datos de la tabla'
END
close desfragmenta /*Cerramos el cursor*/
deallocate desfragmenta
*-------------------------------------------------*--------------------------------------------------*
Saludos
