Automatic Data Collection: Database Files and Logical Drives in MS SQL Server

Total: 2 Average: 5

Introduction

It is important for a database administrator to know when there is no space on a disk. Thus, it is better to automate the process in order for them not to do it manually on each server.

In this article, I am going to describe how to implement automatic daily data collection about logical drives and database files.

Solution

Algorithm:

1. Create tables of data storage:
1.1. for database files:

USE [DATABASE_NAME]
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. for logical drives:

USE [DATABASE_NAME]
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

In addition, you need to fill in a table with logical drives in advance in the following manner:
Server name – volume label

2. create a necessary view for data collection about database files:

USE [DATABASE_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [inf].[ServerDBFileInfo] as
SELECT  @@Servername AS Server ,
        File_id ,--file_id in a database. Its main value always equals 1
        Type_desc ,--description of a file type
        Name as [FileName] ,--logic file name in a database
        LEFT(Physical_Name, 1) AS Drive ,--volume label where a database file is located
        Physical_Name ,--a full name of a file in the operating system
        RIGHT(physical_name, 3) AS Ext ,--file extension
        Size as CountPage, --current file size in pages of 8 Kb
        round((cast(Size*8 as float))/1024,3) as SizeMb, --file size in Mb
        Growth, --growth
        is_percent_growth, --growth in %
        database_id,
        DB_Name(database_id) as [DB_Name]
FROM    sys.master_files--database_files
GO

Here the sys.master_files system view is used.

3. Create a stored procedure that returns information on a logical drive:

USE [DATABASE_NAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [srv].[sp_DriveSpace] 
    @DrivePath varchar(1024) --device (it is possible to set a volume label 'C:')
  , @TotalSpace float output --total volume in bytes
  , @FreeSpace float output  --free disk space in bytes
as
begin

  DECLARE @fso int
        , @Drive int
        , @DriveName varchar(255)
        , @Folder int
        , @Drives int
        , @source varchar(255)
        , @desc varchar(255)
        , @ret int
        , @Object int
  -- Create an object of a file system
  exec @ret = sp_OACreate 'Scripting.FileSystemObject', @fso output
  set @Object = @fso
  if @ret != 0
    goto ErrorInfo

  -- Get a folder on the specified path
  exec @ret = sp_OAmethod @fso, 'GetFolder', @Folder output, @DrivePath  
  set @Object = @fso
  if @ret != 0
    goto ErrorInfo

  -- Get a device
  exec @ret = sp_OAmethod @Folder, 'Drive', @Drive output
  set @Object = @Folder
  if @ret != 0
    goto ErrorInfo

  -- Determine the whole device storage space
  exec @ret = sp_OAGetProperty @Drive, 'TotalSize', @TotalSpace output
  set @Object = @Drive
  if @ret != 0
    goto ErrorInfo

  -- Determine a free space on a disk
  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

To get a detailed information on this procedure, refer to the following article: Disk Space in T-SQL.

4. Create a stored procedure for data collection:

4.1. for database files:

USE [DATABASE_NAME]
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. for logical drives:

USE [DATABASE_NAME]
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. Create views for data output:

5.1. for database files:

USE [DATABASE_NAME]
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. for logical disks:

USE [DATABASE_NAME]
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. Create a task in the SQL Server Agent and run it once a day:

USE [DATABASE_NAME];
GO

exec srv.MergeDBFileInfo;
exec srv.MergeDriverInfo;

7. Collect all the data output from servers. You may do it using the SQL Server Agent, for example.

8. Create a stored procedure for generating a report and sending it to administrators. Since it is possible to implement it in different ways, I will consider it on this particular example:

USE [DATABASE_NAME]
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='When analyzing I have got the data storage devices that either have free disk space less than 15%, or free space decreases over 5% a day:<br><br>'+'<TABLE BORDER=5>';

        set @body=@body+'<TR>';

        set @body=@body+'<TD>';
        set @body=@body+'№ p/p';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'GUID';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'SEVER';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'TOM';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'VOLUME, GB.';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'FREE, GB.';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'FREE SPACE CHANGE, MB.';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'FREE, %';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'FREE SPACE CHANGE, %';
        set @body=@body+'</TD>';

        set @body=@body+'<TD>';
        set @body=@body+'UTC DETECTION TIME';
        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>';
        To get the detailed information, refer to the view SRV.srv.vDrivers<br><br>
        To view the information on database files, refer to the view DATABASE_NAME.srv.vDBFiles';
    end
END

GO

This stored procedure generates an HTML-report about logical drives that either have free disk space less than 15%, or free space decreases over 5% a day. The latter shows a strange activity of records which means that someone stores too much information on this disk very often. It might happen for the following reasons:

  1. It is a time to extend a disk;
  2. It is necessary to delete unused files on a logical drive;
  3. Erase and reduce log files, as well as files of information and other tables.

Solution

In this article, I have analyzed an example of implementing a system of daily automatic data collection about local drives and database files. This information allows finding out in advance which disk has less free space, as well as what database files grow drastically. It allows avoiding a case when there is no space on a disk and finding out a reason why a process takes much space on a disk.

Also read:

Automatic Data Collection of Database Schema Changes in MS SQL Server

Automatic Data Collection about Completed Tasks in MS SQL Server 

Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.