jobgemws 6 ноя 2016 в 12:53

Автосбор данных о файлах баз данных и логических дисках операционной системы в MS SQL Server

10 мин

6.9K

SQL*Microsoft SQL Server*Администрирование баз данных*

Туториал

Предисловие

Администратору баз данных важно знать, когда закончится место на диске. И чтобы этого не делать вручную на каждом сервере, лучше этот процесс автоматизировать.

В данной статье приведу реализацию автоматического ежедневного сбора информации о логических дисках и о файлах баз данных.

Решение

Алгоритм:

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

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) создать задание в Агенте и запускать раз в сутки:

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

Данная хранимая процедура формирует HTML-отчет о логических дисках, у которых либо свободного объема осталось меньше 15%, либо свободное место уменьшается свыше 5% за день. Последнее свидетельствует о странной активности записей (кто-то слишком часто и много пишет на данный диск). Первое же может быть следствием одной из нескольких причин:
1) диск пора увеличивать
2) необходимо удалить неиспользуемое занятое место на логическом диске
3) почистить и сжать файлы лога, а также информационных и прочих таблиц.

Результат

В данной статье был рассмотрен пример реализации системы ежедневного автоматического сбора информации о локальных дисках и файлах баз данных. Данная информация позволяет узнать заранее на каком диске свободного места становится все меньше, а также какие файлы баз данных значительно растут. Это позволяет предотвратить случай, когда места на диске не останется, а также выявить причину того, почему какой-то процесс занимает значительную часть места на диске своими записями и т. д.

Источники:

» sys.master_files
» Объём дисков на T-SQL

Теги:

MS SQL Server

Хабы:

Автосбор данных о файлах баз данных и логических дисках операционной системы в MS SQL Server

Предисловие

Решение

Результат

Источники:

Публикации

Истории

Работа

Ближайшие события