3. SQL Server資料庫狀态監控 - 可用空間

一. 磁盤可用空間；

二. 資料庫可用空間。

資料庫用來存放資料，那麼肯定需要存儲空間，是以對磁盤空間的監視自然就很有必要了。

一. 磁盤可用空間

1. 作業系統指令或腳本、接口或工具

(1) DOS指令: fsutil volume diskfree

C:\windows\system32>fsutil volume diskfree C:

Total # of free bytes : 9789493248

Total # of bytes : 64424505344

Total # of avail free bytes : 9789493248

這裡用到了fsutil，一個檔案系統管理工具(file system utility)，應該還有其他一些指令或者腳本也是可以的。

(2) WMI/WMIC: wmic logicaldisk

WMI是個Windows系統的管理接口，在WMIC出現之前，如果要利用WMI管理系統，必須使用一些專門的WMI應用，例如SMS，或者使用WMI的腳本程式設計API，或者使用象CIM Studio之類的工具。如果不熟悉C++之類的程式設計語言或VBScript之類的腳本語言，或者不掌握WMI名稱空間的基本知識，要用WMI管理系統是很困難的。WMIC改變了這種情況，它為WMI名稱空間提供了一個強大的、友好的指令行接口。

C:\windows\system32>wmic logicaldisk get caption,freespace,size

Caption FreeSpace Size

C: 9789071360 64424505344

D: 189013438464 255331397632

這裡通過wmic的get指令擷取了logicaldisk 的幾個參數列。

(3) 性能螢幕

LogicalDisk: %Free Space

LogicalDisk: Free Megabytes

總大小 = LogicalDisk: Free Megabytes/ LogicalDisk: %Free Space

性能螢幕雖然用于現場診斷還是挺友善的，但實作自動化監控，并不太好用。

2. SQL 語句

(1) 擴充存儲過程xp_cmdshell (還是在調用作業系統指令)

DECLARE @Drive TINYINT,
      @SQL VARCHAR(100)
DECLARE @Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)

SET @Drive = 97
WHILE @Drive <= 122
BEGIN
    SET @SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''

    INSERT @Drives
    (
    Info
    )
    EXEC(@SQL)

    UPDATE @Drives
       SET Drive = CHAR(@Drive)
     WHERE Drive IS NULL

    SET @Drive = @Drive + 1
END

SELECT Drive,
SUM(CASE WHEN Info LIKE 'Total # of bytes%' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024.0/1024/1024 AS TotalMBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes%' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024.0/1024/1024 AS FreeMBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes%' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END)/1024.0/1024/1024 AS AvailFreeMBytes
FROM(
SELECT Drive,
       Info
  FROM @Drives
 WHERE Info LIKE 'Total # of %'
) AS d
GROUP BY Drive
ORDER BY Drive

xp_cmdshell可以執行作業系統指令行，這段腳本用fsutil volume diskfree指令對26個字母的盤符周遊了一遍，不是很好，改用wmic會友善些，如下：

EXEC xp_cmdshell 'wmic logicaldisk get caption,freespace,size';

(2) 擴充存儲過程xp_fixeddrives

--exec xp_fixeddrives
IF object_id('tempdb..#drivefreespace') IS NOT NULL
    DROP TABLE #drivefreespace
CREATE TABLE #drivefreespace(Drive CHAR(1), FreeMb bigint)
INSERT #drivefreespace EXEC ('exec xp_fixeddrives')
SELECT * FROM #drivefreespace

Drive	FreeMb
C	9316
D	180013

總算不依賴作業系統指令了，不過，這個存儲過程隻能傳回磁盤可用空間，沒有磁盤總空間。

(3) DMV/DMF: sys.dm_os_volume_stats

SELECT DISTINCT
       @@SERVERNAME as [server]
      ,volume_mount_point as drive
      ,cast(available_bytes/ 1024.0 / 1024.0 / 1024.0 AS INT) as free_gb
      ,cast(total_bytes / 1024.0 / 1024.0 / 1024.0 AS INT) as total_gb
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
ORDER BY @@SERVERNAME, volume_mount_point

server	drive	free_gb	total_gb
…	C:\	9	59
	D:\	175	237

從SQL Server 2008 R2 SP1開始，有了這個很好用的DMF: sys.dm_os_volume_stats，彌補了之前xp_fixeddrives沒有磁盤總空間的不足。

不過，看它的參數就可以知道，沒被任何資料庫使用的磁盤，是檢視不了的，是以xp_fixeddrives還有存在的必要。

二. 資料庫可用空間

1. 檔案可用空間檢視

(1) 檔案已用空間，目前大小(已配置設定空間)，最大值，如下：

select @@SERVERNAME as server_name
      ,DB_NAME() as database_name
      ,case when data_space_id = 0 then 'LOG'
            else FILEGROUP_NAME(data_space_id) 
            end as file_group
      ,name as logical_name
      ,physical_name
      ,type_desc
      ,FILEPROPERTY(name,'SpaceUsed')/128.0 as used_size_Mb
      ,size/128.0 as allocated_size_mb 
      ,case when max_size = -1 then max_size 
            else max_size/128.0 
            end as max_size_Mb
      ,growth
      ,is_percent_growth
 from sys.database_files
where state_desc = 'ONLINE'

(2) 再算上磁盤的空閑空間，改動如下：

select @@SERVERNAME as server_name
      ,DB_NAME() as database_name
      ,case when data_space_id = 0 then 'LOG'
            else FILEGROUP_NAME(data_space_id) 
            end as file_group
      ,name as logical_name
      ,physical_name
      ,type_desc
      ,FILEPROPERTY(name,'SpaceUsed')/128.0 as used_size_mb
      ,size/128.0 as allocated_size_mb
      ,case when max_size = -1 then max_size 
                else max_size/128.0 
                end as max_size_mb
      ,vs.available_bytes/1024.0/1024 as disk_free_mb
      ,growth
      ,CAST(is_percent_growth as int) as is_percent_growth
from sys.database_files df
cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vs
where state_desc = 'ONLINE'

如果是SQL Server 2008 SP1以前的版本，可用xp_fixeddrives生成磁盤空閑空間表，再進行關聯。

(3) 結合檔案是否自增長，檔案最大值，磁盤空間，算出檔案可用空間比率，改動如下：

select @@SERVERNAME as server_name
      ,DB_NAME() as database_name
      ,case when data_space_id = 0 then 'LOG'
            else FILEGROUP_NAME(data_space_id) 
            end as file_group
      ,name as logical_name
      ,physical_name
      ,type_desc
      ,FILEPROPERTY(name,'SpaceUsed')/128.0 as used_size_mb
      ,size/128.0 as allocated_size_mb
      ,case when max_size = -1 then max_size 
                else max_size/128.0 
                end as max_size_mb
      ,vs.available_bytes/1024.0/1024 as disk_free_mb
      ,case when growth = 0 then  (size - FILEPROPERTY(name,'SpaceUsed'))*1.0/size
            when growth > 0 and max_size = -1 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,'SpaceUsed')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
            when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) >= 0 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,'SpaceUsed')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
            when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) <  0 then (max_size - FILEPROPERTY(name,'SpaceUsed'))*1.0/max_size
            else null 
            end as free_space_percent
      ,growth
      ,CAST(is_percent_growth as int) as is_percent_growth
from sys.database_files df
cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vs
where state_desc = 'ONLINE'

(4) 如果有多個資料庫，注意fileproperty()和filegroup_name()函數，都隻在目前資料庫下生效，改動如下：

if object_id('tempdb..#tmp_filesize') is not null
    drop table #tmp_filesize
GO
create table #tmp_filesize
(
server_name          varchar(256),
database_name        varchar(256),
file_group           varchar(256),
logical_name         varchar(256),
physical_name        varchar(1024),
type_desc            varchar(128),
used_size_mb         float,
allocated_size_mb    float,
max_size_mb          float,
disk_free_mb         float,
free_space_percent   float,
growth               int,
is_percent_growth    int
)
GO

exec sp_msforeachdb 'use [?]  
insert into #tmp_filesize
select @@SERVERNAME as server_name
      ,DB_NAME() as database_name
      ,case when data_space_id = 0 then ''LOG''
            else FILEGROUP_NAME(data_space_id) 
            end as file_group
      ,name as logical_name
      ,physical_name
      ,type_desc
      ,FILEPROPERTY(name,''SpaceUsed'')/128.0 as used_size_mb
      ,size/128.0 as allocated_size_mb
      ,case when max_size = -1 then max_size 
                else max_size/128.0 
                end as max_size_mb
      ,vs.available_bytes/1024.0/1024 as disk_free_mb
      ,case when growth = 0 then  (size - FILEPROPERTY(name,''SpaceUsed''))*1.0/size
            when growth > 0 and max_size = -1 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,''SpaceUsed'')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
            when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) >= 0 then ((size/128.0 + vs.available_bytes/1024.0/1024) - FILEPROPERTY(name,''SpaceUsed'')/128.0)/(size/128.0 + vs.available_bytes/1024.0/1024)
            when growth > 0 and max_size <> -1 and (max_size/128.0 - vs.available_bytes/1024.0/1024) <  0 then (max_size - FILEPROPERTY(name,''SpaceUsed''))*1.0/max_size
            else null 
            end as free_space_percent
      ,growth
      ,CAST(is_percent_growth as int) as is_percent_growth
from sys.database_files df
cross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vs
where state_desc = ''ONLINE'''

select * from #tmp_filesize

2. 資料庫可用空間告警

2.1 告警的格式

資料庫可用空間告警，通常不告警某個檔案，也不告警整個資料庫，而是某個确切的檔案組/表空間，日志檔案是沒有檔案組的，所有可以把日志檔案合并為LOG這個組。

(1) Oracle可以給表空間設定最大尺寸，表空間裡的每個檔案逐個使用，直到最後一個檔案也沒空間時，就會提示空間不足；

(2) SQL Server 無法對檔案組設定最大尺寸，隻可以給檔案組裡每個檔案指定最大尺寸，是以要先統計：是否目前檔案組下所有的檔案都已經滿了？

将同一個檔案組/LOG下的所有檔案都檢查一下，如果所有檔案都滿了(以20%為例)，那麼就滿足告警條件了，如下：

--#tmp_filesize 在上面的腳本裡生成了
select server_name,
       database_name,
       file_group,
       MAX(free_space_percent) as max_free_space_percent
  from #tmp_filesize
 group by server_name,database_name,file_group
 having MAX(free_space_percent) <= 0.2 --20%

郵件告警的格式大緻為：

郵件标題：主機名\執行個體名\資料庫名\檔案組名，@@servername已經包含了SQL Server執行個體名；

郵件内容：檔案組 ”file group name” 空間不足，已低于20%。

2.2 告警後如何處理？

(1) 告警中的檔案組裡的檔案，所在的磁盤還有空間嗎？

exec xp_fixeddrives

如果目前磁盤沒空間，可以給目前檔案組在其他磁盤上添加新的檔案，并關閉老的檔案自增長或限制最大值；

如果所有磁盤都沒空間，可以考慮删除磁盤上的其他檔案，或者收縮資料庫檔案(資料/日志)，或者磁盤擴充空間(加磁盤)。

(2) 如果磁盤有空間，檔案是否關閉了自動增長？

可能是在建立檔案時，給了檔案比較大的size，如500G，并關閉了檔案自動增長；

ALTER DATABASE test
ADD FILE 
(
    NAME = test_02,
    FILENAME = 'D:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_02.ndf',
    SIZE = 500 GB,
    FILEGROWTH = 0
)
TO FILEGROUP [PRIMARY];
GO

(3) 如果磁盤有空間，自動增長也開了，是不是限制了檔案最大值？

限制最大值和關閉自增長，應該都是不想單個檔案變得太大，個人覺得一個檔案控制在500G以内比較合理，這兩種情況，都建議擴充一個新檔案。

小結

如果沒有監控工具，那麼可選擇系統視圖，擴充存儲過程，結合資料庫郵件的方式，作自動檢查，并告警檔案組/日志空閑空間不足。大緻步驟如下：

(1) 部署資料庫郵件；

(2) 部署作業：定時檢查檔案組/日志空閑空間，發郵件告警。

3. SQL Server資料庫狀态監控 - 可用空間

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述