主页 / dba / 问题 / 230109
Accepted
Learning_DBAdmin
Asked: 2019-02-19 22:36:01 +0800 CST

数据库维护作业和备份计划

  • 772

我会要求专家就安排维护和备份工作提供建议。以下是我更改之前的场景：

  1. 数据库的完整备份计划在每天凌晨 12:30 运行。
  2. 差异备份计划在工作时间（上午 8 点到下午 6 点）每 2 小时运行一次，在非工作时间每 6 小时运行一次。
  3. 配置日志传送后，日志备份计划每 15 分钟运行一次。
  4. 索引优化作业（使用来自 Great Mr. Ola Hallengren 的脚本）在每周日凌晨 1:45 运行。

在上述场景中，我们曾经面临存储磁盘上的空间问题，因为在维护作业之前运行了完整备份，因此后续差异备份的大小越来越大，直到运行下一次完整备份。这促使我在维护作业后运行完整备份，我还在周中检查了碎片级别，并根据决定每周运行两次维护作业的值，以下是修改后的计划：

  1. 计划在所有日期的凌晨 12:30 运行数据库的完整备份，但星期日和星期二安排维护作业时除外。在周日和周二，完整备份在凌晨 2:30 进行。
  2. 差异备份计划在工作时间（上午 8 点到下午 6 点）每 2 小时运行一次，在非工作时间每 6 小时运行一次 - 没有变化。
  3. 日志备份计划每 15 分钟运行一次，因为已配置日志传送 - 无变化
  4. 索引优化作业（使用 Great Mr. Ola Hallengren 的脚本）每周日和周二凌晨 1:45 运行。

我现在面临的问题是维护工作后立即进行日志备份的大小，日志备份比数据库本身的完整备份大得多。不用说，日志备份被转移到辅助站点，然后上传到那里用于同步目的。这花费的时间比预期的要长，并且在日志传送警报被触发之间，因为主要和次要不同步。同样早些时候，日志备份更大，但比完整备份要小得多，并且从主服务器传输到辅助服务器所花费的时间要少得多。

我不太确定，如果这是一个有效的场景，其中更改（插入/更新/删除）在过去 3 天内如此庞大以至于维护作业创建了比完整备份更大的日志文件并且会逐渐稳定下来或者我应该安排两个周日和周二的完整备份（维护作业运行时）- 一个在凌晨 12:30，另一个在维护作业之后。

感谢您的建议。

sql-server backup

2 个回答

  1. Best Answer

    根据计划的维护任务和二次备份策略，这是我的建议，可能适合您减少事务日志备份的要求。正如您在评论中所说，“为数据库启用了日志传送。日志传送功能支持 FULL 和 BULK_LOGGED 恢复模型。

    所以

    如果您想继续使用 FULL 恢复模式，您可以做的第一件事。

    您可以在计划维护作业中添加两个步骤：

    1. 将数据库的恢复模式更改为 BULK_LOGGED
    2. 执行索引维护脚本
    3. 并将恢复模式更改回 FULL

    优点：它将减少截断日志备份大小。缺点：在此期间无法进行时间点恢复。

    如果它适合您的 SLA（RTO 和 RPO），您可以做的第二件事是每 15 分钟备份一次事务日志，“将恢复模式更改为 BULK_LOGGED。

    您可以执行的第三个操作是找到所有未使用的索引并删除它们，这也有助于减少 T-Log 备份大小和维护时间。您可以使用此脚本查找索引详细信息。

    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

    第四，您需要验证您是否为索引使用了适当的 FILLFACTOR。更少的 FILLFACTOR 意味着更多的页数。所以根据表格的用途选择了FILLFACTOR。

    谢谢！

    • 0

  2. 除了上述之外，您还可以安排您的日志备份在索引维护期间不运行，并重新安排您的完整备份在维护作业之后运行。您还需要将备份日志设置为空，以便在维护作业期间运行。完整备份将获取所有更改，但前提是您在此过程中清除了日志。否则 full 将运行并且您将有另一个大型日志备份需要处理。我对大型 ETL 负载做了类似的事情。

    索引维护会创建大量事务日志记录。您可以做的另一件事是每天进行维护。这将导致更小的日志备份。

    我几年前处理过同样的问题，改用日常维护很有帮助。它不再是每周 3-6 小时的流程，而是每天 15-30 分钟的流程。

    • 0

相关问题