收集整個 SQL Server 實例的磁碟空間指標

我對 SQL Server 很陌生。我想收集整個 SQL Server 實例的數據庫磁碟空間指標。

我找到了一個可以收集每個數據庫的磁碟空間資訊的程式碼範例。

IF OBJECT_ID('DISK.dbo.disk_activity') IS NOT NULL
   DROP TABLE disk_activity

CREATE TABLE disk_activity (
      servername NVARCHAR(100)
   ,  database_id INT PRIMARY KEY
   , name NVARCHAR(MAX)
   , data_used_size DECIMAL(18,2)
   , log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
   SELECT '
   USE [' + d.name + ']
   INSERT INTO DISK.dbo.disk_activity (servername, database_id, name, data_used_size, log_used_size)
   SELECT
         (SELECT @@SERVERNAME AS "Server Name") AS SERVER_NAME
       , DB_ID()
       , DB_NAME()
       , SUM(CASE WHEN [type] = 0 THEN space_used END)
       , SUM(CASE WHEN [type] = 1 THEN space_used END)
   FROM (
       SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
       FROM sys.database_files s
       GROUP BY s.[type]
   ) t;'
   FROM sys.databases d
   WHERE d.[state] = 0
   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

但是，我想收集整個伺服器的指標，即為所有數據庫分配的總磁碟空間、已使用的總磁碟空間、可用的總磁碟空間和已用磁碟空間的百分比。

我應該如何修改上述腳本或是否有其他可用選項？

為了監控磁碟空間使用情況和數據庫文件大小，我使用了這個腳本，你可以在我的部落格上找到：

-- create a temporary table to hold data from sys.master_files
IF OBJECT_ID('tempdb..#masterfiles') IS NOT NULL
   DROP TABLE #masterfiles;

CREATE TABLE #masterfiles (
   database_id int,
   type_desc varchar(10),
   name sysname,
   physical_name varchar(255),
   size_mb int,
   max_size_mb int,
   growth int,
   is_percent_growth bit,
   data_space_id int,
   data_space_name nvarchar(128) NULL,
   drive nvarchar(512),
   mbfree int
);

-- extract file information from sys.master_files
-- and correlate each file to its logical volume
INSERT INTO #masterfiles
SELECT
    mf.database_id
   ,type_desc
   ,name
   ,physical_name
   ,size_mb = size / 128
   ,max_size_mb =
       CASE
           WHEN max_size = 268435456 AND type_desc = 'LOG' THEN -1
           ELSE
               CASE
                   WHEN max_size = -1 THEN -1
                   ELSE max_size / 128
               END
       END
   ,mf.growth
   ,mf.is_percent_growth
   ,mf.data_space_id
   ,NULL
   ,d.volume_mount_point
   ,d.available_bytes / 1024 / 1024
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id) AS d;

-- add an "emptyspace" column to hold empty space for each file
ALTER TABLE #masterfiles ADD emptyspace_mb int NULL;

-- iterate through all databases to calculate empty space for its files
DECLARE @name sysname;

DECLARE c CURSOR FORWARD_ONLY READ_ONLY STATIC LOCAL
FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'

OPEN c
FETCH NEXT FROM c INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

   DECLARE @sql nvarchar(max)
   DECLARE @statement nvarchar(max)
   SET @sql = '
       UPDATE mf
       SET emptyspace_mb = size_mb - FILEPROPERTY(name,''SpaceUsed'') / 128,
           data_space_name =
               ISNULL(
                   (SELECT name FROM sys.data_spaces WHERE data_space_id = mf.data_space_id),
                   ''LOG''
               )
       FROM #masterfiles AS mf
       WHERE database_id = DB_ID();
   '
   SET @statement = 'EXEC ' + QUOTENAME(@name) + '.sys.sp_executesql @sql'
   EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql

   FETCH NEXT FROM c INTO @name
END

CLOSE c
DEALLOCATE c

-- create a scalar function to simulate the growth of the database in the drive's available space
IF OBJECT_ID('tempdb..calculateAvailableSpace') IS NOT NULL
   EXEC tempdb.sys.sp_executesql N'DROP FUNCTION calculateAvailableSpace'

EXEC tempdb.sys.sp_executesql N'
CREATE FUNCTION calculateAvailableSpace(
   @diskFreeSpaceMB float,
   @currentSizeMB float,
   @growth float,
   @is_percent_growth bit
)
RETURNS int
AS
BEGIN
   IF @currentSizeMB = 0
       SET @currentSizeMB = 1
   DECLARE @returnValue int = 0
   IF @is_percent_growth = 0
   BEGIN
       SET @returnValue = (@growth /128) * CAST((@diskFreeSpaceMB / (@growth / 128)) AS int)
   END
   ELSE
   BEGIN
       DECLARE @prevsize AS float = 0
       DECLARE @calcsize AS float = @currentSizeMB
       WHILE @calcsize < @diskFreeSpaceMB
       BEGIN
           SET @prevsize = @calcsize
           SET @calcsize = @calcsize + @calcsize * @growth / 100.0
       END
       SET @returnValue = @prevsize - @currentSizeMB
       IF @returnValue < 0
           SET @returnValue = 0
   END

   RETURN @returnValue
END
'

-- report database filegroups with less than 20% available space
;WITH masterfiles AS (
   SELECT *
       ,available_space =
           CASE mf.max_size_mb
               WHEN -1 THEN tempdb.dbo.calculateAvailableSpace(mbfree, size_mb, growth, is_percent_growth)
               ELSE max_size_mb - size_mb
           END
           + emptyspace_mb
   FROM #masterfiles AS mf
),
spaces AS (
   SELECT
        DB_NAME(database_id) AS database_name
       ,data_space_name
       ,type_desc
       ,SUM(size_mb) AS size_mb
       ,SUM(available_space) AS available_space_mb
       ,SUM(available_space) * 100 /
           CASE SUM(size_mb)
               WHEN 0 THEN 1
               ELSE SUM(size_mb)
           END AS available_space_percent
   FROM masterfiles
   GROUP BY DB_NAME(database_id)
       ,data_space_name
       ,type_desc
)
SELECT *
FROM spaces
WHERE available_space_percent < 20
ORDER BY available_space_percent ASC

IF OBJECT_ID('tempdb..#masterfiles') IS NOT NULL
   DROP TABLE #masterfiles;

IF OBJECT_ID('tempdb..calculateAvailableSpace') IS NOT NULL
   EXEC tempdb.sys.sp_executesql N'DROP FUNCTION calculateAvailableSpace'

基本上，它用於sys.master_files查詢所有數據庫文件的大小並sys.dm_os_volume_stats查詢驅動器資訊。FILEPROPERTY返回有關文件內可用空間的資訊。

該腳本用於在可用空間低於門檻值（例如 20%）時發出警報，但可以對其進行修改以執行定期監控並將資訊記錄在集中表中。

不要忽視數據收集器功能可以在管理數據倉庫數據庫中記錄的內容。該功能在 SQL Server 的所有版本中都可用（由於缺少調度收集的代理而被排除在外）。如果你的報表不夠，你可以直接查詢表（你只需要[snapshots].[disk_usage]在MDW數據庫​​中查詢）。

特別是，如果您使用的是企業版，則實用程序控制點功能通過“整體”方法專注於資源利用。同樣，它提供了一些報告和儀表板來查看整個伺服器基礎架構或單個伺服器的資源使用率。

根據我的經驗，Data Collector 和 Utility Control Point 是 SQL Server 中使用最少的功能。

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