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

　　一. 磁盤可用空間

　　(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

　　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

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

　　(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(

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

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) 再算上磁盤的空閑空間，改動如下：

,vs.available_bytes/1024.0/1024 as disk_free_mb

,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

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

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

,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

(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

exec sp_msforeachdb 'use [?]

insert into #tmp_filesize

,case when data_space_id = 0 then ''log''

,fileproperty(name,''spaceused'')/128.0 as used_size_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

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) 部署作業：定時檢查檔案組/日志空閑空間，發郵件告警。

最新内容請見作者的github頁：http://qaseven.github.io/