Trabalho de manutenção de banco de dados e agendamento de backup

De acordo com as tarefas de manutenção programada e a segunda estratégia de backup, aqui está minha sugestão que pode atender às suas necessidades de reduzir o backup do log de transações. Como você disse no comentário, “O Log-Shipping está habilitado para os bancos de dados. O recurso Log-Shipping suporta o modelo de recuperação FULL e BULK_LOGGED.

Então

A primeira coisa que você pode fazer se quiser continuar com o modelo de recuperação COMPLETO.

Você pode adicionar duas etapas no trabalho de manutenção programada:

1. Altere o modelo de recuperação do banco de dados para BULK_LOGGED
2. Executar script de manutenção do índice
3. E altere o modelo de recuperação de volta para FULL

Prós: reduzirá o tamanho do backup do log de truncamento. Contras: A recuperação pontual não será possível durante a duração.

2ª coisa que você pode fazer se for adequado ao seu SLA (RTO e RPO), pois você está fazendo backup do log de transações a cada 15 minutos, “altere o modelo de recuperação para BULK_LOGGED.

3ª ação que você pode executar para encontrar todos os índices não utilizados e removê-los, isso ajudará a reduzir o tamanho do backup do T-Log e a duração da manutenção também. Você pode usar este script para encontrar detalhes do índice.

CREATE TABLE #tbl_index_info(
    [Table Name] [sysname] NOT NULL,
    [Index Name] [sysname] NULL,
    [Index Type] [nvarchar](60) NULL,
    [Index Columns] [sysname] NULL,
    [Row Count] [bigint] NULL,
    [Fill Factor] [tinyint] NOT NULL,
    [user_seeks] [bigint] NOT NULL,
    [user_scans] [bigint] NOT NULL,
    [user_lookups] [bigint] NOT NULL,
    [Index Read] [bigint] NULL,
    [Index Writes] [bigint] NOT NULL,
    [Last Used (Read)] [datetime] NULL,
    [Last Used (Write)] [datetime] NULL,
    [Last Index Re-Built/Re-Organize] [datetime] NULL,
    [index_id] [int] NOT NULL,
    [object_id] [int] NOT NULL
) 
GO

insert into #tbl_index_info
select      t.name [Table Name]
            ,i.name [Index Name]
            ,i.type_desc [Index Type]
            ,c.name [Index Columns]
            ,p.rows [Row Count]
            ,i.fill_factor [Fill Factor]
            ,ius.user_seeks
            ,ius.user_scans
            ,ius.user_lookups
            ,(ius.user_seeks+ius.user_scans+ius.user_lookups) [Index Read]
            ,ius.user_updates [Index Writes]
            ,COALESCE(ius.last_user_seek,ius.last_user_scan,ius.last_user_lookup) [Last Used (Read)]
            ,ius.last_user_update [Last Used (Write)]
            ,STATS_DATE(s.object_id, s.stats_id) [Last Index Re-Built/Re-Organize]
            ,i.index_id
            ,t.object_id

from        sys.tables t
inner join  sys.columns c
on          t.object_id=c.object_id
inner join  sys.indexes i
on          t.object_id=i.object_id
inner join  sys.index_columns ic
on          t.object_id=ic.object_id and i.index_id=ic.index_id and c.column_id=ic.column_id
inner join  sys.dm_db_index_usage_stats ius
on          t.object_id=ius.object_id and i.index_id=ius.index_id
inner join  sys.stats s
on          t.object_id=s.object_id and i.index_id=s.stats_id
inner join  sys.partitions p
on          p.object_id=i.object_id and p.index_id=i.index_id
where i.name is not null and i.is_unique!=1
Order by ius.user_lookups desc
GO

Select      DISTINCT
            a.[Table Name],a.[Index Name],a.[Index Type],
            (SELECT SUBSTRING(
            (
                SELECT ',' + [Index Columns] FROM #tbl_index_info b
                WHERE a.[Table Name]=b.[Table Name] and a.[Index Name]=b.[Index Name]
                FOR XML PATH('')), 2,10000)
                )[Index Columns]
                ,a.[Row Count]
            ,ips.avg_fragmentation_in_percent,a.[Fill Factor],a.user_seeks,a.user_scans
            ,a.user_lookups,a.[Index Read],a.[Index Writes],a.[Last Used (Read)],a.[Last Used (Write)]
            ,[Last Index Re-Built/Re-Organize]
from
#tbl_index_info a
cross apply
            sys.dm_db_index_physical_stats(db_id(),a.object_id,a.index_id,0,DEFAULT) ips
ORDER BY [Index Read] ASC
GO

DROP TABLE #tbl_index_info
GO

4º você precisa verificar se está usando FILLFACTOR apropriado para índices. Menos FILLFACTOR significa mais número de páginas. Portanto, escolha FILLFACTOR de acordo com o uso da tabela.

Obrigado!

Além do acima, você pode agendar para que seus backups de log não sejam executados durante a manutenção do índice e reagendar seu total para execução após o trabalho de manutenção. Você precisará configurar um log de backup como nulo também para executar durante o trabalho de manutenção. O backup completo pegará todas as alterações, mas somente se você limpar o log durante o processo. Caso contrário, o full será executado e você terá outro backup de log grande para lidar. Eu faço algo semelhante para grandes cargas ETL.

A manutenção do índice cria muitos logs de log de transações. Outra coisa que você pode fazer é executar a manutenção diariamente. Isso resultará em backups de log menores.

Eu lidei com os mesmos problemas anos atrás e mudar para a manutenção diária ajudou. Em vez de um processo semanal de 3 a 6 horas, tornou-se um processo diário de 15 a 30 minutos.