數(shù)據(jù)庫用來存放數(shù)據(jù)，那么肯定需要存儲空間，所以對磁盤空間的監(jiān)視自然就很有必要了。

一. 磁盤可用空間

1. 操作系統(tǒng)命令或腳本、接口或工具

(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，一個文件系統(tǒng)管理工具(file system utility)，應該還有其他一些命令或者腳本也是可以的。

(2) WMI/WMIC: wmic logicaldisk

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

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

Caption FreeSpace Size

C: 9789071360 64424505344

D: 189013438464 255331397632

這里通過wmic的get命令獲取了logicaldisk 的幾個參數(shù)列。

(3) 性能監(jiān)視器

LogicalDisk: %Free Space

LogicalDisk: Free Megabytes

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

性能監(jiān)視器雖然用于現(xiàn)場診斷還是挺方便的，但實現(xiàn)自動化監(jiān)控，并不太好用。

2. SQL 語句

(1) 擴展存儲過程xp_cmdshell (還是在調用操作系統(tǒng)命令)

DECLARE @Drive TINYINT,      @SQL VARCHAR(100)DECLARE @Drives TABLE(Drive CHAR(1),Info VARCHAR(80))SET @Drive = 97WHILE @Drive <= 122BEGIN    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 + 1ENDSELECT 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 AvailFreeMBytesFROM(SELECT Drive,       Info  FROM @Drives WHERE Info LIKE 'Total # of %') AS dGROUP BY DriveORDER BY Drive

xp_cmdshell可以執(zhí)行操作系統(tǒng)命令行，這段腳本用fsutil volume diskfree命令對26個字母的盤符遍歷了一遍，不是很好，改用wmic會方便些，如下：

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

(2) 擴展存儲過程xp_fixeddrives

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

總算不依賴操作系統(tǒng)命令了，不過，這個存儲過程只能返回磁盤可用空間，沒有磁盤總空間。

(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_gbFROM sys.master_files AS fCROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)ORDER BY @@SERVERNAME, volume_mount_point

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

不過，看它的參數(shù)就可以知道，沒被任何數(shù)據(jù)庫使用的磁盤，是查看不了的，所以xp_fixeddrives還有存在的必要。

二. 數(shù)據(jù)庫可用空間

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_fileswhere 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_growthfrom sys.database_files dfcross apply sys.dm_os_volume_stats(DB_ID(),df.file_id) vswhere state_desc = 'ONLINE'

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

(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