MS SQL Serverのオペレーティングシステムのデータベースファイルおよび論理ドライブ上のデータの自動収集

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 

 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 

 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 

 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 

 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 

 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 

 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 

 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 

 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