監視 SQL Server 數據文件中的可用空間

我已手動將 mdf/ndf 文件的大小調整為很大，以避免對 SQL Server 數據庫進行自動增長操作。由於文件更大，磁碟分區上的可用空間很少，系統管理員不斷提醒我空間不足。

因為我調整了它們的大小，所以數據文件中有很多可用空間，但在查看文件大小/磁碟可用空間時不會注意到它。

如何監控數據文件的實際使用百分比？我更喜歡使用性能計數器。我擔心當文件真的空間不足時，SQL Server 將無法分配足夠的空間並崩潰。

當您可以從簡單的查詢中獲取性能計數器時，不確定為什麼要為此使用性能計數器。Log File(s) Size (KB)事實上，雖然您可以從性能計數器 ( / )中獲取有關日誌文件的這些資訊Log File(s) Used Size (KB)，但對於數據文件中使用了多少空間，並沒有這樣的計數器。

;WITH f AS 
(
 SELECT name, size = size/128.0 FROM sys.database_files
),
s AS
(
 SELECT name, size, free = size-CONVERT(INT,FILEPROPERTY(name,'SpaceUsed'))/128.0
 FROM f
)
SELECT name, size, free, percent_free = free * 100.0 / size
FROM s;

我有另一種方法可以主動監控數據文件空間，並在可用空間低於某個百分比時使用 SQL Alert 發出警報。

基礎是

• 在 sys.messages 中創建使用者定義的錯誤消息。這將由 sql 代理警報使用。

-- User-defined error messages can be an integer between 50001 and 2147483647. 
EXEC sp_addmessage 
 @msgnum=911421, -- 911DBA
 @severity=1,    -- Informational message not generated by DB Engine       
 @msgtext=N'Data files are %d percent full in database %s.'

• 現在創建一個 SQL 代理作業。確保set @threshold = 20 --->>>>>>>>>>>>>>>>> CHANGE HERE <<<<<<<<<<<<<<<<<<<<<---在下面的腳本中進行更改。我已經把他作為一個非常低的門檻值，只是為了模擬警報。安排作業每 30 分鐘執行一次（根據您的需要進行更改）。

   if object_id('tempdb..#dbserversize') is not null
   DROP TABLE #dbserversize;

   create table dbo.#dbserversize (
    [id] int identity (1,1)
    ,[databaseName] sysname
   ,[Drive]    varchar(3)
   ,[Logical Name] sysname
   ,[Physical Name]    varchar(max)
   ,[File Size MB] decimal(38, 2)
   ,[Space Used MB]    decimal(38, 2)
   ,[Free Space]   decimal(38, 2)
   ,[%Free Space]  decimal(38, 2)
   ,[Max Size] varchar(max)
   ,[Growth Rate]  varchar(max)
   )

   declare @id int
   declare @threshold int
   declare @dbname sysname

   declare @sqltext nvarchar(max)

   declare @freespacePct int

   set @threshold = 20   --->>>>>>>>>>>>>>>>> CHANGE HERE <<<<<<<<<<<<<<<<<<<<<---

   select @dbname = min(name) from sys.databases where database_id > 4 and [state] = 0 

   while @dbname is not NULL

   begin
       select @dbname = name from sys.databases where name = @dbname and database_id > 4 and [state] = 0 
           --- Modified from Erin's blog : Proactive SQL Server Health Checks, Part 1 : Disk Space
           --- source http://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
       set @sqltext =  ' use '+@dbname+';'+' 
           insert into dbo.#dbserversize
           select '''+@dbname+''' as [databaseName]
               ,substring([physical_name], 1, 3) as [Drive]
               ,[name] as [Logical Name]
               ,[physical_name] as [Physical Name]
               ,cast(CAST([size] as decimal(38, 2)) / 128.0 as decimal(38, 2)) as [File Size MB]
               ,cast(CAST(FILEPROPERTY([name], ''SpaceUsed'') as decimal(38, 2)) / 128.0 as decimal(38, 2)) as [Space Used MB]
               ,cast((CAST([size] as decimal(38, 0)) / 128) - (CAST(FILEPROPERTY([name], ''SpaceUsed'') as decimal(38, 0)) / 128.) as decimal(38, 2)) as [Free Space]
               ,cast(((CAST([size] as decimal(38, 2)) / 128) - (CAST(FILEPROPERTY([name], ''SpaceUsed'') as decimal(38, 2)) / 128.0)) * 100.0 / (CAST([size] as decimal(38, 2)) / 128) as decimal(38, 2)) as [%Free Space]
               ,case 
                   when cast([max_size] as varchar(max)) = - 1
                       then ''UNLIMITED''
                   else cast([max_size] as varchar(max))
                   end as [Max Size]
               ,case 
                   when is_percent_growth = 1
                       then cast([growth] as varchar(20)) + ''%''
                   else cast([growth] as varchar(20)) + ''MB''
                   end as [Growth Rate]
               from sys.database_files
               where type = 0 -- for Rows , 1 = LOG'
           --print @sqltext
           exec (@sqltext)


           select @dbname = min(name) from sys.databases where name > @dbname and database_id > 4 and [state] = 0 
   end


   --- delete the entries that do not meet the threshold 

   delete from dbo.#dbserversize
   where [%Free Space] < @threshold;


   --select * from dbo.#dbserversize

   --- NOW Raise errors for the databases that we got flagged up

   while exists (select null from dbo.#dbserversize)
   begin

       select top 1 @id = id,
                   @dbname = databaseName,
                   @freespacePct = [%Free Space]
               from dbo.#dbserversize;


           RAISERROR(911421, 10,1,@freespacePct, @dbname) with LOG;

           delete from dbo.#dbserversize where id = @id;

   end

• 現在創建一個警報以響應911421錯誤號。

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'MDF file alert', 
       @message_id=911421, 
       @severity=0, 
       @enabled=1, 
       @delay_between_responses=1800, 
       @include_event_description_in=0, 
       @job_id=N'019c4770-865b-406b-894e-72a1ff34f732'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'MDF file alert', @operator_name=N'Notify 911 DBA for MDF files getting full', @notification_method = 1
GO

注意：您可以使用我上面的想法進行其他類型的增強。

• 將代理作業作為接受門檻值作為輸入值的儲存過程。
• 將值記錄到 DBA 數據庫中的物理表中，以便您進行趨勢分析和容量規劃。
• 你能想到的任何其他…… :-)

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