none
Como desfragmentar Bancos de Dados RRS feed

Respostas

  •  

    Prezada Camila Leroy,

     

    Para desframentar sua base de dados siga os passos do KB943345.

     

    Abaixo segue o script para rodar no SQL.

     

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_DefragIndexes')
    BEGIN
    DROP  Procedure  dbo.proc_DefragIndexes
    END
    GO
    CREATE PROCEDURE dbo.proc_DefragIndexes
    (
    @onlineDefragThreshold float = 10.0,
    @offlineDefragThreshold float = 90.0,
    @updateStatsThreshold int = 7
    )
     AS
    BEGIN
    set nocount on
    DECLARE @objectid int
    DECLARE @indexid int
    DECLARE @frag float
    DECLARE @command varchar(8000)
    DECLARE @schemaname sysname
    DECLARE @objectname sysname
    DECLARE @indexname sysname
    declare @AllIndexes table (objectid int, indexid int, fragmentation float)
    declare @currentDdbId int
    select @currentDdbId = DB_ID()
    insert into @AllIndexes
    SELECT
    object_id, index_id, avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (@currentDdbId, NULL, NULL , NULL, 'LIMITED')
    WHERE index_id > 0
    DECLARE indexesToDefrag CURSOR FOR SELECT * FROM @AllIndexes
    OPEN indexesToDefrag;
    -- Loop through the partitions.
    FETCH NEXT
    FROM indexesToDefrag
    INTO @objectid, @indexid, @frag;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT @schemaname = 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 = name
    FROM sys.indexes
    WHERE  object_id = @objectid AND index_id = @indexid
    IF @frag > @onlineDefragThreshold
    BEGIN
    IF @frag < @offlineDefragThreshold
    BEGIN;
    SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + object_name(@objectid) + ' REORGANIZE'
    EXEC (@command)
    END;
    IF @frag >= @offlineDefragThreshold
    BEGIN;
    SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + object_name(@objectid) + ' REBUILD'
    EXEC (@command)
    END;
    PRINT 'Executed ' + @command
    END
    IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, getdate())
    BEGIN
    SELECT @command = 'UPDATE STATISTICS ' + @schemaname + '.' + object_name(@objectid) + ' ' + @indexname +' WITH RESAMPLE'
    EXEC (@command)
    PRINT 'Executed ' + @command
    END
    FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag
    END
    CLOSE indexesToDefrag;
    DEALLOCATE indexesToDefrag;
    END
    GO

    Espero ter ajudado,

     

    Atenciosamente,

     

    Heber Lopes

    São José dos Campos

    msn: heber.lopes@gmail.com

     

    Caso este post tenha respondido sua pergunta marque como resposta para auxiliar em futuras pesquisas.

    sexta-feira, 14 de março de 2008 14:01