I'd decided to build that monitoring to prevent any disk space issues, it's the complement from my Power BY Dashboard monitoring I have been enhancing, also, that has been very useful for my daily chores.
This is the mobile interface:
My SQL Dashboard Monitoring:
--==================================================================
--// In C# - I built a CLR to check the disk space from OS
--==================================================================
using System;
using System.Data.SqlTypes;
using System.IO;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString fnDiskSpace()
{
DriveInfo[] allDrives = DriveInfo.GetDrives();
string JsonStr = "[";
int count = 1;
foreach (DriveInfo d in allDrives)
{
if (d.IsReady == true)
{
JsonStr += "{\"DriveName\":\"" + d.Name.Replace(@"\",@"\\") + "\",";
JsonStr += "\"DriveType\":\"" + d.DriveType.ToString() + "\",";
JsonStr += "\"VolumeLabel\":\"" + d.VolumeLabel.ToString() + "\",";
JsonStr += "\"DriveFormat\":\"" + d.DriveFormat.ToString() + "\",";
JsonStr += "\"TotalFreeSpace\":\"" + d.TotalFreeSpace.ToString() + "\",";
JsonStr += "\"TotalSize\":\"" + d.TotalSize.ToString() + "\",";
JsonStr += "\"FreeSpace\":\"" + d.AvailableFreeSpace.ToString() + "\"}";
if (count != allDrives.Length)
JsonStr += ",";
}
count++;
}
JsonStr += "]";
return new SqlString (JsonStr);
}
}
--==================================================================
--// Create a table to save the Data
--==================================================================
USE [DBA]
GO
/****** Object: Table [dbo].[ServerDisk] Script Date: 12/11/2018 11:22:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
drop table [dbo].[ServerDisk]
go
CREATE TABLE [dbo].[ServerDisk](
[ServerDiskID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NULL,
[PerformanceIndicatorValue] [float] NOT NULL,
[DriveName] [varchar](100) NULL,
[DriveType] [varchar](100) NULL,
[DriveFormat] [varchar](100) NULL,
[VolumeLabel] [varchar](100) NULL,
[TotalFreeSpaceBytes] bigint NULL,
[FreeSpaceBytes] bigint NULL,
[TotalSizeBytes] bigint NULL,
[UsedSpaceBytes] as ([TotalSizeBytes] - [FreeSpaceBytes]),
[UsedSpaceMB] as (([TotalSizeBytes] - [FreeSpaceBytes])/1024/1024),
[UsedSpaceGB] as (([TotalSizeBytes] - [FreeSpaceBytes])/1024/1024/1024),
[FreeSpaceMB] as ([FreeSpaceBytes]/1024/1024),
[FreeSpaceGB] as ([FreeSpaceBytes]/1024/1024/1024),
[TotalSizeMB] as ([TotalSizeBytes]/1024/1024),
[TotalSizeGB] as ([TotalSizeBytes]/1024/1024/1024),
[MinimalSpaceMB] bigint NULL default(0),
[TargetSpaceMB] bigint NULL default(0),
[TargetMaxUsedSpaceMB] as ( ([TotalSizeBytes]/1024/1024) - [TargetSpaceMB]),
[TargetMaxUsedSpaceGB] as ( ([TotalSizeBytes]/1024/1024/1024) - ([TargetSpaceMB]/1024)),
[TargetSpaceGB] as ([TargetSpaceMB]/1024),
[DiskDateID] [int] NULL,
[ModifiedDateOn] [datetime] NULL,
[CreatedDateOn] [datetime] NOT NULL,
CONSTRAINT [ServerDisk_pk] PRIMARY KEY CLUSTERED
(
[ServerDiskID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--==================================================================
--// It saves the Data in SQL
--==================================================================
CREATE or Alter proc [dbo].[sp_DBAWEB_Disk]
As
/*****************************************************************************************************************
Name: dbo.sp_DBAWEB_Disk
View all the SQL Disk status.
*****************************************************************************************************************
Change History
*****************************************************************************************************************
Date: Author: Description:
----- ------- -------------------
12 Nov 2018 Leandro Buffone Created Initial Version
*****************************************************************************************************************
Usage:
set statistics io on
set statistics time on
Exec sp_DBAWEB_Disk
*****************************************************************************************************************/
Declare @ServerName varchar(100) = @@ServerName,
@PerformanceIndicatorCategoryName varchar(100) = 'Disk',
@PerformanceIndicatorName varchar(100) = 'Disk Space',
@json NVARCHAR(MAX)
SET @json=(select DBA.[dbo].[fnDiskSpace]())
Merge dbo.ServerDisk d
Using (
SELECT
ServerName = @ServerName,
PerformanceIndicatorCategoryName = @PerformanceIndicatorCategoryName,
PerformanceIndicatorName = @PerformanceIndicatorName,
PerformanceAVGIndicatorValue = 0,
PerformanceIndicatorValue = 0,
DriveName,
DriveType,
DriveFormat,
VolumeLabel,
TotalFreeSpaceBytes,
FreeSpaceBytes,
TotalSizeBytes,
DiskDateID = dbo.fnDateKey(getdate()),
ModifiedDateOn = getdate(),
CreatedDateOn = getdate()
FROM
OPENJSON ( @json )
WITH (
DriveName varchar(200) '$.DriveName' ,
DriveType varchar(200) '$.DriveType',
DriveFormat varchar(200) '$.DriveFormat',
VolumeLabel varchar(200) '$.VolumeLabel',
TotalFreeSpaceBytes bigint '$.TotalFreeSpace',
FreeSpaceBytes bigint '$.FreeSpace',
TotalSizeBytes bigint '$.TotalSize'
)
) s on s.DriveName = d.DriveName
when matched
then
update
set
DriveName = s.DriveName,
DriveType = s.DriveType,
DriveFormat = s.DriveFormat,
VolumeLabel = s.VolumeLabel,
TotalFreeSpaceBytes = s.TotalFreeSpaceBytes,
FreeSpaceBytes = s.FreeSpaceBytes,
TotalSizeBytes = s.TotalSizeBytes,
DiskDateID = s.DiskDateID
when not matched then
Insert ( ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, DriveName, DriveType, DriveFormat, VolumeLabel, TotalFreeSpaceBytes, FreeSpaceBytes, TotalSizeBytes, DiskDateID, ModifiedDateOn, CreatedDateOn)
values ( ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, DriveName, DriveType, DriveFormat, VolumeLabel, TotalFreeSpaceBytes, FreeSpaceBytes, TotalSizeBytes, DiskDateID, ModifiedDateOn, CreatedDateOn)
when not matched by source then
delete;
GO
--==================================================================
--// It set up each disks with the treshoulderoulders
--==================================================================
--//==================== CG1
update serverDisk set TargetSpaceMB = '50' where DriveName = 'Q:\'
update serverDisk set TargetSpaceMB = '100000' where DriveName = 'L:\'
update serverDisk set TargetSpaceMB = '200000' where DriveName = 'K:\'
update serverDisk set TargetSpaceMB = '50' where DriveName = 'G:\'
update serverDisk set TargetSpaceMB = '40000' where DriveName = 'C:\'
--//==================== CG2
update serverDisk set TargetSpaceMB = '50' where DriveName = 'H:\'
update serverDisk set TargetSpaceMB = '200000' where DriveName = 'F:\'
update serverDisk set TargetSpaceMB = '10000' where DriveName = 'E:\'
update serverDisk set TargetSpaceMB = '40000' where DriveName = 'C:\'
--//==================== SQL4
update serverDisk set TargetSpaceMB = '100000' where DriveName = 'E:\'
update serverDisk set TargetSpaceMB = '200000' where DriveName = 'D:\'
update serverDisk set TargetSpaceMB = '40000' where DriveName = 'C:\'
--==================================================================
--// I'm collecting from all the servers to my monitoring server
--==================================================================
CREATE or Alter PROCEDURE [REAL].[spDBAWEBLoadServerDisk]
@ServerName varchar(60) = 'All'
as
/*****************************************************************************
Name: REAL.spDBAWEBLoadServerDisk
Load the Real-Time table REAL.ServerDisk from a operational table previously populated in DBA.
******************************************************************************
Change History
******************************************************************************
Date: Author: Description:
----- ------- -------------------
10 Act 2018 Leandro Buffone Created Initial Version
******************************************************************************
Usage:
exec REAL.spDBAWEBLoadServerDisk
******************************************************************************/
--// Clean up all the data
Delete REAL.ServerDisk
If @ServerName = 'All' or @ServerName = 'TG-SVR-CG1\PRD' Begin
merge REAL.ServerDisk d
using (
--// CG1
--==================================
Select
top 1
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = 1,
CreatedDateOn
from DBAWEB_CG1.DBA.DBO.ServerDisk
Where TargetSpaceMB > FreeSpaceMB
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End
If @ServerName = 'All' or @ServerName = 'TG-SVR-CG2\PRD2' Begin
merge REAL.ServerDisk d
using (
--// CG2
--==================================
Select
top 1
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = 1,
CreatedDateOn
from DBAWEB_CG2.DBA.DBO.ServerDisk
Where TargetSpaceMB > FreeSpaceMB
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End
If @ServerName = 'All' or @ServerName = 'TG-SVR-SQL4\PRD' Begin
merge REAL.ServerDisk d
using (
--// SQL4
--==================================
Select
top 1
ServerName,
PerformanceIndicatorCategoryName,
PerformanceIndicatorName,
PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = 1,
CreatedDateOn
from DBA.DBO.ServerDisk
Where TargetSpaceMB > FreeSpaceMB
) s on s.ServerName = d.ServerName and s.PerformanceIndicatorCategoryName = d.PerformanceIndicatorCategoryName and s.PerformanceIndicatorName = d.PerformanceIndicatorName
when matched
then
update
set
PerformanceAVGIndicatorValue = s.PerformanceAVGIndicatorValue,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
CreatedDateOn = s.CreatedDateOn
when not matched then
insert (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn)
values (ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, CreatedDateOn);
End
GO