Автосбор данных о файлах баз данных и логических дисках операционной системы в 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