まえがき
データベース管理者は、ディスク容量が不足したことを知ることが重要です。 また、各サーバーで手動でこれを行わないために、このプロセスを自動化することをお勧めします。
この記事では、論理ドライブとデータベースファイルに関する情報の毎日の自動収集の実装を提供します。
解決策
アルゴリズム：
1）情報を保存するためのテーブルを作成します。
1.1）データベースファイルの場合：
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[DBFile]( [DBFile_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [Server] [nvarchar](255) NOT NULL, [Name] [nvarchar](255) NOT NULL, [Drive] [nvarchar](10) NOT NULL, [Physical_Name] [nvarchar](255) NOT NULL, [Ext] [nvarchar](255) NOT NULL, [Growth] [int] NOT NULL, [IsPercentGrowth] [int] NOT NULL, [DB_ID] [int] NOT NULL, [DB_Name] [nvarchar](255) NOT NULL, [SizeMb] [float] NOT NULL, [DiffSizeMb] [float] NOT NULL, [InsertUTCDate] [datetime] NOT NULL, [UpdateUTCdate] [datetime] NOT NULL, [File_ID] [int] NOT NULL, CONSTRAINT [PK_DBFile] PRIMARY KEY CLUSTERED ( [DBFile_GUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [srv].[DBFile] ADD CONSTRAINT [DF_DBFile_DBFile_GUID] DEFAULT (newid()) FOR [DBFile_GUID] GO ALTER TABLE [srv].[DBFile] ADD CONSTRAINT [DF_DBFile_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO ALTER TABLE [srv].[DBFile] ADD CONSTRAINT [DF_DBFile_UpdateUTCdate] DEFAULT (getutcdate()) FOR [UpdateUTCdate] GO
1.2）論理ドライブの場合：
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[Drivers]( [Driver_GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [Server] [nvarchar](255) NOT NULL, [Name] [nvarchar](8) NOT NULL, [TotalSpace] [float] NOT NULL, [FreeSpace] [float] NOT NULL, [DiffFreeSpace] [float] NOT NULL, [InsertUTCDate] [datetime] NOT NULL, [UpdateUTCdate] [datetime] NOT NULL, CONSTRAINT [PK_Drivers] PRIMARY KEY CLUSTERED ( [Driver_GUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_Driver_GUID] DEFAULT (newid()) FOR [Driver_GUID] GO ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_Server] DEFAULT (@@servername) FOR [Server] GO ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_TotalSpace] DEFAULT ((0)) FOR [TotalSpace] GO ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_FreeSpace] DEFAULT ((0)) FOR [FreeSpace] GO ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_DiffFreeSpace] DEFAULT ((0)) FOR [DiffFreeSpace] GO ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO ALTER TABLE [srv].[Drivers] ADD CONSTRAINT [DF_Drivers_UpdateUTCdate] DEFAULT (getutcdate()) FOR [UpdateUTCdate] GO
論理ドライブを含むテーブルは、次のように事前に入力する必要があります。
サーバー名-ボリュームラベル（MyServer-C :)。
2）データベースファイルに関する情報を収集するために必要なプレゼンテーションを作成します。
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[ServerDBFileInfo] as SELECT @@Servername AS Server , File_id ,-- . file_id 1 Type_desc ,-- Name as [FileName] ,-- LEFT(Physical_Name, 1) AS Drive ,-- , Physical_Name ,-- RIGHT(physical_name, 3) AS Ext ,-- Size as CountPage, -- 8 round((cast(Size*8 as float))/1024,3) as SizeMb, -- Growth, -- is_percent_growth, -- database_id, DB_Name(database_id) as [DB_Name] FROM sys.master_files--database_files GO
sys.master_filesシステムビューを使用します
3）論理ドライブの情報を返すストアドプロシージャを作成します。
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [srv].[sp_DriveSpace] @DrivePath varchar(1024) -- ( 'C:') , @TotalSpace float output -- , @FreeSpace float output -- as begin DECLARE @fso int , @Drive int , @DriveName varchar(255) , @Folder int , @Drives int , @source varchar(255) , @desc varchar(255) , @ret int , @Object int -- exec @ret = sp_OACreate 'Scripting.FileSystemObject', @fso output set @Object = @fso if @ret != 0 goto ErrorInfo -- exec @ret = sp_OAmethod @fso, 'GetFolder', @Folder output, @DrivePath set @Object = @fso if @ret != 0 goto ErrorInfo -- exec @ret = sp_OAmethod @Folder, 'Drive', @Drive output set @Object = @Folder if @ret != 0 goto ErrorInfo -- exec @ret = sp_OAGetProperty @Drive, 'TotalSize', @TotalSpace output set @Object = @Drive if @ret != 0 goto ErrorInfo -- exec @ret = sp_OAGetProperty @Drive, 'AvailableSpace', @FreeSpace output set @Object = @Drive if @ret != 0 goto ErrorInfo DestroyObjects: if @Folder is not null exec sp_OADestroy @Folder if @Drive is not null exec sp_OADestroy @Drive if @fso is not null exec sp_OADestroy @fso return (@ret) ErrorInfo: exec sp_OAGetErrorInfo @Object, @source output, @desc output print 'Source error: ' + isnull( @source, 'n/a' ) + char(13) + 'Description: ' + isnull( @desc, 'n/a' ) goto DestroyObjects; end GO
このストアドプロシージャは、次の記事T-SQL Disk Capacity
4）情報を収集するためのストアドプロシージャを作成します。
4.1）データベースファイルの場合：
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[MergeDBFileInfo] AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; ;merge [srv].[DBFile] as f using [inf].[ServerDBFileInfo] as ff on f.File_ID=ff.File_ID and f.DB_ID=ff.[database_id] and f.[Server]=ff.[Server] when matched then update set UpdateUTcDate = getUTCDate() ,[Name] = ff.[FileName] ,[Drive] = ff.[Drive] ,[Physical_Name] = ff.[Physical_Name] ,[Ext] = ff.[Ext] ,[Growth] = ff.[Growth] ,[IsPercentGrowth] = ff.[is_percent_growth] ,[SizeMb] = ff.[SizeMb] ,[DiffSizeMb] = round(ff.[SizeMb]-f.[SizeMb],3) when not matched by target then insert ( [Server] ,[Name] ,[Drive] ,[Physical_Name] ,[Ext] ,[Growth] ,[IsPercentGrowth] ,[DB_ID] ,[DB_Name] ,[SizeMb] ,[File_ID] ,[DiffSizeMb] ) values ( ff.[Server] ,ff.[FileName] ,ff.[Drive] ,ff.[Physical_Name] ,ff.[Ext] ,ff.[Growth] ,ff.[is_percent_growth] ,ff.[database_id] ,ff.[DB_Name] ,ff.[SizeMb] ,ff.[File_id] ,0 ) when not matched by source and f.[Server]=@@SERVERNAME then delete; END GO
4.2）論理ドライブの場合：
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[MergeDriverInfo] AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; declare @Drivers table ( [Server] nvarchar(255), Name nvarchar(8), TotalSpace float, FreeSpace float, DiffFreeSpace float NULL ); insert into @Drivers ( [Server], Name, TotalSpace, FreeSpace ) select [Server], Name, TotalSpace, FreeSpace from srv.Drivers where [Server]=@@SERVERNAME; declare @TotalSpace float; declare @FreeSpace float; declare @DrivePath nvarchar(8); while(exists(select top(1) 1 from @Drivers where DiffFreeSpace is null)) begin select top(1) @DrivePath=Name from @Drivers where DiffFreeSpace is null; exec srv.sp_DriveSpace @DrivePath = @DrivePath , @TotalSpace = @TotalSpace out , @FreeSpace = @FreeSpace out; update @Drivers set TotalSpace=@TotalSpace ,FreeSpace=@FreeSpace ,DiffFreeSpace=case when FreeSpace>0 then round(FreeSpace-@FreeSpace,3) else 0 end where Name=@DrivePath; end ;merge [srv].[Drivers] as d using @Drivers as dd on d.Name=dd.Name and d.[Server]=dd.[Server] when matched then update set UpdateUTcDate = getUTCDate() ,[TotalSpace] = dd.[TotalSpace] ,[FreeSpace] = dd.[FreeSpace] ,[DiffFreeSpace]= dd.[DiffFreeSpace]; END GO
5）情報出力の表現を作成します。
5.1）データベースファイルの場合：
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view [srv].[vDBFiles] as SELECT [DBFile_GUID] ,[Server] ,[Name] ,[Drive] ,[Physical_Name] ,[Ext] ,[Growth] ,[IsPercentGrowth] ,[DB_ID] ,[File_ID] ,[DB_Name] ,[SizeMb] ,[DiffSizeMb] ,round([SizeMb]/1024,3) as [SizeGb] ,round([DiffSizeMb]/1024,3) as [DiffSizeGb] ,round([SizeMb]/1024/1024,3) as [SizeTb] ,round([DiffSizeMb]/1024/1024,3) as [DiffSizeTb] ,round([DiffSizeMb]/([SizeMb]/100), 3) as [DiffSizePercent] ,[InsertUTCDate] ,[UpdateUTCdate] FROM [srv].[DBFile]; GO
5.2）論理ドライブの場合：
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view [srv].[vDrivers] as select [Driver_GUID] ,[Server] ,[Name] ,[TotalSpace] as [TotalSpaceByte] ,[FreeSpace] as [FreeSpaceByte] ,[DiffFreeSpace] as [DiffFreeSpaceByte] ,round([TotalSpace]/1024, 3) as [TotalSpaceKb] ,round([FreeSpace]/1024, 3) as [FreeSpaceKb] ,round([DiffFreeSpace]/1024, 3) as [DiffFreeSpaceKb] ,round([TotalSpace]/1024/1024, 3) as [TotalSpaceMb] ,round([FreeSpace]/1024/1024, 3) as [FreeSpaceMb] ,round([DiffFreeSpace]/1024/1024, 3) as [DiffFreeSpaceMb] ,round([TotalSpace]/1024/1024/1024, 3) as [TotalSpaceGb] ,round([FreeSpace]/1024/1024/1024, 3) as [FreeSpaceGb] ,round([DiffFreeSpace]/1024/1024/1024, 3) as [DiffFreeSpaceGb] ,round([TotalSpace]/1024/1024/1024/1024, 3) as [TotalSpaceTb] ,round([FreeSpace]/1024/1024/1024/1024, 3) as [FreeSpaceTb] ,round([DiffFreeSpace]/1024/1024/1024/1024, 3) as [DiffFreeSpaceTb] ,round([FreeSpace]/([TotalSpace]/100), 3) as [FreeSpacePercent] ,round([DiffFreeSpace]/([TotalSpace]/100), 3) as [DiffFreeSpacePercent] ,[InsertUTCDate] ,[UpdateUTCdate] FROM [srv].[Drivers] GO
6）エージェントでタスクを作成し、1日に1回実行します。
USE [__]; GO exec srv.MergeDBFileInfo; exec srv.MergeDriverInfo;
7）サーバーから受信したすべての情報を収集します（エージェントのタスクまたは他の方法でも可能です）
8）レポートを生成して管理者に送信するためのストアドプロシージャを作成します。 さまざまな方法で実装できます。 したがって、例のみを示します。
コード
USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[GetHTMLTableShortInfoDrivers] @body nvarchar(max) OUTPUT AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; declare @tbl table ( Driver_GUID uniqueidentifier ,[Name] nvarchar(255) ,[TotalSpaceGb] float ,[FreeSpaceGb] float ,[DiffFreeSpaceMb] float ,[FreeSpacePercent] float ,[DiffFreeSpacePercent] float ,UpdateUTCDate datetime ,[Server] nvarchar(255) ,ID int identity(1,1) ); declare @Driver_GUID uniqueidentifier ,@Name nvarchar(255) ,@TotalSpaceGb float ,@FreeSpaceGb float ,@DiffFreeSpaceMb float ,@FreeSpacePercent float ,@DiffFreeSpacePercent float ,@UpdateUTCDate datetime ,@Server nvarchar(255) ,@ID int; insert into @tbl( Driver_GUID ,[Name] ,[TotalSpaceGb] ,[FreeSpaceGb] ,[DiffFreeSpaceMb] ,[FreeSpacePercent] ,[DiffFreeSpacePercent] ,UpdateUTCDate ,[Server] ) select Driver_GUID ,[Name] ,[TotalSpaceGb] ,[FreeSpaceGb] ,[DiffFreeSpaceMb] ,[FreeSpacePercent] ,[DiffFreeSpacePercent] ,UpdateUTCDate ,[Server] from srv.vDrivers where [DiffFreeSpacePercent]<=-5 or [FreeSpacePercent]<=15 order by [Server] asc, [Name] asc; if(exists(select top(1) 1 from @tbl)) begin set @body=' , 15%, 5% :<br><br>'+'<TABLE BORDER=5>'; set @body=@body+'<TR>'; set @body=@body+'<TD>'; set @body=@body+'№ /'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+''; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+''; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+''; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+', .'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+', .'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+' , .'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+', %'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+' , %'; set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+'UTC '; set @body=@body+'</TD>'; set @body=@body+'</TR>'; while((select top 1 1 from @tbl)>0) begin set @body=@body+'<TR>'; select top 1 @Driver_GUID = Driver_GUID ,@Name = Name ,@TotalSpaceGb = TotalSpaceGb ,@FreeSpaceGb = FreeSpaceGb ,@DiffFreeSpaceMb = DiffFreeSpaceMb ,@FreeSpacePercent = FreeSpacePercent ,@DiffFreeSpacePercent = DiffFreeSpacePercent ,@UpdateUTCDate = UpdateUTCDate ,@Server = [Server] ,@ID = [ID] from @tbl; set @body=@body+'<TD>'; set @body=@body+cast(@ID as nvarchar(max)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+cast(@Driver_GUID as nvarchar(255)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+coalesce(@Server,''); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+coalesce(@Name,''); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+cast(@TotalSpaceGb as nvarchar(255)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+cast(@FreeSpaceGb as nvarchar(255)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+cast(@DiffFreeSpaceMb as nvarchar(255)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+cast(@FreeSpacePercent as nvarchar(255)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+cast(@DiffFreeSpacePercent as nvarchar(255)); set @body=@body+'</TD>'; set @body=@body+'<TD>'; set @body=@body+rep.GetDateFormat(@UpdateUTCDate, default)+' '+rep.GetTimeFormat(@UpdateUTCDate, default); set @body=@body+'</TD>'; delete from @tbl where ID=@ID; set @body=@body+'</TR>'; end set @body=@body+'</TABLE>'; set @body=@body+'<br><br> SRV.srv.vDrivers<br><br> __.srv.vDBFiles'; end END GO
このストアドプロシージャは、15％未満の空き容量が残っているか、空き容量が1日あたり5％以上削減されている論理ドライブに関するHTMLレポートを生成します。 後者は、奇妙な記録活動を示しています（誰かがこのディスクに頻繁に書き込みすぎる）。 最初の原因は、いくつかの理由のいずれかの結果です。
1）ディスクを増やすときです
2）論理ドライブ上の未使用の占有スペースを削除する必要があります
3）ログファイル、情報、その他のテーブルをクリーンアップして圧縮します。
結果
この記事では、ローカルディスクとデータベースファイルに関する情報を収集するための毎日の自動システムを実装する例を検討しました。 この情報により、どのディスク空き領域が少なくなっているのか、またどのデータベースファイルが大幅に増加しているのかを事前に知ることができます。 これにより、ディスクにスペースが残っていない場合を防ぐことができ、プロセスがレコードなどでディスクスペースのかなりの部分を占める理由を特定することもできます。
ソース：
» Sys.master_files
» T-SQLディスク容量