[MSSQL] 好胖的MSDB

2016-06-22

626
0
MSSQL

我們單位管理MSSQL的規範之一，就是把SYSTEM DB跟USER DB放不同的磁碟空間，避免特定的USER DB把空間用光影響到整台Server，

哪知道這次的兇手就是SYSTEM DB...

某一天收到alert說某一台Server的磁碟空間不足，想說又是哪個USER DB在搞怪把磁碟空間用完了，

哪知道再仔細一看，居然是存在SYSTEM DB的磁碟用完了，

趕緊連進去看一下，這下不得了，MSDB長大到將近70GB...

下SQL確認到底是哪個TABLE把空間都用光了，

SELECT sc.name + '.' + t.NAME AS TableName,
       p.[Rows],
       ( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB,
       ( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB,
       ( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB
FROM msdb.sys.tables t
       INNER JOIN msdb.sys.schemas sc ON sc.schema_id = t.schema_id
       INNER JOIN msdb.sys.indexes i ON t.OBJECT_ID = i.object_id
       INNER JOIN msdb.sys.partitions p ON i.object_id = p.OBJECT_ID
                  AND i.index_id = p.index_id
       INNER JOIN msdb.sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.type_desc = 'USER_TABLE'
      AND i.index_id <= 1  --- Heap\ CLUSTERED
GROUP BY sc.name + '.' + t.NAME,
      i.[object_id],i.index_id, i.name, p.[Rows]
ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC

結果看到是存放Mail的LOG以及Mail Body的Table佔用最多的空間，

檢查Table的資料發現，PM在透過這台Server來當作發送告警或資訊的主機，但在設計之初沒有想過資料成長的速度會這麼快，

短短不到半年，就一發不可收拾，

在與PM討論之後的資料生命週期及確認這些資訊的使用必要之後，我們共同定義了只保留約七天的資料，

因此就開始了瘦身之旅...

微軟本身有提供幾隻Store Procedure來對這發送Mail的Table進行purge，分別如下：

--Mail Body--
--- sysmail_delete_mailitems_sp  @sent_before , @sent_status
--- sent_status = sent, unsent, retrying, failed and null. NULL indicates all statuses.
EXECUTE msdb..sysmail_delete_mailitems_sp '2016/1/1',NULL -- 刪除早於2016/1/1以前的信件，且不限制發送結果

--Mail Log--
--- sysmail_delete_log_sp @logged_before,@event_type
--- event_type = success, warning, error, informational and null. NULL indicates all event types.
EXECUTE msdb..sysmail_delete_log_sp '2016/1/1',NULL -- 刪除早於2016/1/1以前的記錄，且不限制事件種類

　等到資料都刪掉之後，再針對MSDB進行Shrink

DBCC SHRINKDATABASE ('MSDB')

之後等到瘦身結束之後，目前的Size剩下大概10G左右，真的算是最成功的減肥了...

不過還是要強調一下，SD真的很重要，不能每次都等到事情發生了再來救火呀。

回首頁