數據庫維護作業和備份計劃

我會要求專家就安排維護和備份作業提供建議。以下是我更改之前的場景：

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，另一個在維護工作之後。

感謝您的好意建議。

根據計劃的維護任務和第二次備份策略，我的建議可能適合您減少事務日誌備份的要求。正如您在評論中所說，“為數據庫啟用了日誌傳送。日誌傳送功能支持 FULL 和 BULK_LOGGED 恢復模式。

所以

如果您想繼續使用完全恢復模式，您可以做的第一件事。

您可以在計劃維護作業中添加兩個步驟：

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。

謝謝！

引用自：https://dba.stackexchange.com/questions/230109