I have many DBs that are currently used for insert and delete. For maintenance i create a procedure that:
-Shrink Database file (if is enabled)
-Shrink Database log file
-Search all indexes of a table that have 5% or more of fragmentation and page_count >= 1000 (According this Microsoft Web about page_count)
-Rebuild indexes > 30% fragmentation and Reorganize indexes between 5% and 30% (According to this Microsoft Web)
-Shrink Database log file of the DB
I execute the procedure between trimester and annual depends use of the DB.
The database file is shrink only when specified with exec spIndexDefrag 1
Value 0 always shrink database log file at begin and finish process.
For now I use 1 in @DB_SHRINK ONLY IN SOME CASES THAT ARE REQUIRED FOR DISK SPACE. Only Shrink the log file.
Is safe do that?
This is my procedure, is applicable to any DB, only need to select the DB in management.
CREATE PROCEDURE dbo.spIndexDefrag @DB_SHRINK bit
AS
DECLARE @COUNT_INDEXES INT = 1
DECLARE @TOTALTEMP_INDEXES INT
DECLARE @COUNT_TABLES INT = 1
DECLARE @TOTALTEMP_TABLES INT
DECLARE @QUERY VARCHAR (MAX) = ''
DECLARE @INDEXNAME VARCHAR (50)
DECLARE @TABLENAME VARCHAR (50)
DECLARE @FRAGMENTATION DECIMAL(18,2)
DECLARE @DBNAME VARCHAR (50) = DB_NAME()
DECLARE @LOGICALDBNAME VARCHAR (50)
DECLARE @LOGICALDBNAME_LOG VARCHAR (50)
DECLARE @QUERYSHRINK VARCHAR (300)
DECLARE @QUERYSHRINK_DB_ENABLED VARCHAR (100) = ''
DECLARE @NEWLINE AS CHAR(2) = CHAR(13) + CHAR(10)
--Nombre logico de la base de datos
SELECT @LOGICALDBNAME = name
FROM sys.master_files
WHERE database_id = db_id()
AND type = 0
--Nombre logico del log de la base de datos
SELECT @LOGICALDBNAME_LOG = name
FROM sys.master_files
WHERE database_id = db_id()
AND type = 1
--Si esta habilitado el shrink de la DB se incluye el codigo en la variable
IF @DB_SHRINK = 1
BEGIN
SET @QUERYSHRINK_DB_ENABLED = 'DBCC SHRINKFILE('''+@LOGICALDBNAME+''', 2)'
END
--Se hace un shrink a la base de datos (Si esta disponible) y posteriormente al log
SET @QUERYSHRINK = '
ALTER DATABASE ['+@DBNAME+'] SET RECOVERY SIMPLE WITH NO_WAIT
'+@QUERYSHRINK_DB_ENABLED+'
DBCC SHRINKFILE('''+@LOGICALDBNAME_LOG+''', 2)
ALTER DATABASE ['+@DBNAME+'] SET RECOVERY FULL WITH NO_WAIT'
EXEC (@QUERYSHRINK)
/*
EL LOG DE LA BASE DE DATOS SE TRUNCA ANTES Y DESPUES
DE REORGANIZAR Y RECONSTRUIR INDICES DE LA TABLA
*/
--Selecciono todas las tablas y vistas en la base de dato y la inserto en tabla temporal
SELECT ROW_NUMBER() OVER (Order by TABLE_NAME) AS ID, TABLE_NAME
INTO #TEMPTABLES
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE IN ('BASE TABLE','VIEW') AND TABLE_CATALOG = @DBNAME
SELECT @TOTALTEMP_TABLES = COUNT(ID) FROM #TEMPTABLES --Total de tablas y vistas
--Si existen tablas
IF @TOTALTEMP_TABLES > 0
BEGIN
--Mientras haya tablas a verificar
WHILE @COUNT_TABLES <= @TOTALTEMP_TABLES
BEGIN
--Obtengo nombre de la tabla
SELECT @TABLENAME = TABLE_NAME FROM #TEMPTABLES WHERE ID = @COUNT_TABLES
/*
Busco todos los indices de la tabla que tengan
mas de un 5% de fragmentacion y que tengan 1000
o mas en page_count y las inserto en tabla temporal
*/
SELECT ROW_NUMBER() OVER (Order by avg_fragmentation_in_percent) AS ID,
Name, avg_fragmentation_in_percent as Fragmentation
INTO #TEMPINDEXES
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TABLENAME), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE name IS NOT NULL
AND avg_fragmentation_in_percent > 5.00
AND page_count >= 1000
SELECT @TOTALTEMP_INDEXES = COUNT(ID) FROM #TEMPINDEXES --Total de indices en tabla
--Si la tabla tiene indices
IF @TOTALTEMP_INDEXES > 0
BEGIN
SET @COUNT_INDEXES = 1 --Reseteo contador de indices
--Bucle que crea el query por cada indice de la tabla
WHILE @COUNT_INDEXES <= @TOTALTEMP_INDEXES
BEGIN
SELECT @INDEXNAME = Name FROM #TEMPINDEXES WHERE ID = @COUNT_INDEXES
SELECT @FRAGMENTATION = Fragmentation FROM #TEMPINDEXES WHERE ID = @COUNT_INDEXES
/*
Si la fragmentacion del indice es menor o igual a 30%
entonces el indice sera reorganizado, de lo contrario
sera reconstruido
*/
IF @FRAGMENTATION <= 30.00 BEGIN
IF EXISTS (SELECT Name FROM sys.indexes WHERE name = @INDEXNAME AND object_id = OBJECT_ID(@TABLENAME))
BEGIN
SET @QUERY += 'ALTER INDEX ' + @INDEXNAME + ' ON ' + @TABLENAME + ' REORGANIZE' + @NEWLINE
END
END
ELSE
BEGIN
IF EXISTS (SELECT Name FROM sys.indexes WHERE name = @INDEXNAME AND object_id = OBJECT_ID(@TABLENAME))
BEGIN
SET @QUERY += 'ALTER INDEX ' + @INDEXNAME + ' ON ' + @TABLENAME + ' REBUILD' + @NEWLINE
END
END
SET @COUNT_INDEXES = @COUNT_INDEXES + 1
END
END
DROP TABLE #TEMPINDEXES --Elimino tabla de indices
SET @COUNT_TABLES = @COUNT_TABLES + 1 --Vuelvo con otra tabla el mismo proceso
END
EXEC (@QUERY) --Ejecuto el query creado para defragmentar indices
DROP TABLE #TEMPTABLES --Elimino tabla de Tablas
SET @QUERYSHRINK = REPLACE(@QUERYSHRINK,@QUERYSHRINK_DB_ENABLED,'')
EXEC (@QUERYSHRINK) --Ejecuto el shrink del log por ultima vez pero sin shrink DB
END
GO
Thanks!
