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:
- It is a time to extend a disk;
- It is necessary to delete unused files on a logical drive;
- 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
Tags: database administration, sql server Last modified: September 23, 2021
