info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
SQL Space Monitor - Version 2
by BF (Principal Consultant; Architecture; Engineering)
2017-07-12
SQL Space Monitor - Version 2:
Captures, Logs, Alerts & Reports for Microsoft SQL Server Database Data and Log File Space plus Disk Space.
We have included in Version 2 the logic for Warning and Alert Thresholds - per Database Data and Log Files Internal Space and Disk Space - as parameters and also added the ability to email a HTML generated report. The report has row-level markers that differentiate each type of space monitor and easily displays to the user the items needing attention. Version 2 also includes a permanent table for logging of all system & user databases space and all disk mounts space.
If needed, Version 1 is located here: T-SQL Get Databases File Sizes
T-SQL Solution:
Create Logging Table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LOG_SPACE_DB_DISK](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[DB] [sysname] NOT NULL,
[LogicalFileName] [nvarchar](100) NULL,
[PhysicalFileName] [nvarchar](500) NULL,
[FileID] [int] NULL,
[FileSizeMB] [decimal](18, 2) NULL,
[FileFreeSpaceMB] [decimal](18, 2) NULL,
[FileFreeSpace%] [decimal](18, 2) NULL,
[DiskMount] [nvarchar](5) NULL,
[DiskSizeGB] [decimal](18, 2) NULL,
[DiskFreeSpaceGB] [decimal](18, 2) NULL,
[DiskFreeSpace%] [decimal](18, 2) NULL,
[InsertedDate] [datetime] NULL,
[PeriodEnding] [datetime] NULL,
CONSTRAINT [PK_LOG_SPACE_DB_DISK] PRIMARY KEY CLUSTERED
(
[LogID] 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
Create Stored Procedure:
Create Procedure dbo.sp_Server_Space_DB_DISK
@WarningThresholdPercent decimal(18,0) = 10, -- <10%
@WarningThresholdValue decimal(18,0) = 10, -- <5GB
@AlertThresholdPercent decimal(18,0) = 5, -- <5%
@AlertThresholdValue decimal(18,0) = 5, -- <1GB
@EmailReport int = 1
as
/*
Reference: www.TechDevOps.com
*/
Set nocount on
Declare @PeriodEnding datetime;
Declare @IsWarnings bit = 0;
Declare @IsAlerts bit = 0;
Declare @tableHTML nvarchar(max);
Set @PeriodEnding = GetDate();
Create Table #TMP
(
[DB] sysname,
[LogicalFileName] nvarchar(100),
[PhysicalFileName] nvarchar(500),
[FileID] int,
[FileSizeMB] decimal (18,2),
[FileFreeSpaceMB] decimal (18,2),
[FileFreeSpace%] decimal (18,2),
[DiskMount] nvarchar(5),
[DiskSizeGB] decimal (18,2),
[DiskFreeSpaceGB] decimal (18,2),
[DiskFreeSpace%] decimal (18,2),
)
Exec sp_msforeachdb '
Use [?];
INSERT INTO #TMP (DB, LogicalFileName, PhysicalFileName, FileID, FileSizeMB, FileFreeSpaceMB, [FileFreeSpace%], [DiskMount], DiskSizeGB, DiskFreeSpaceGB, [DiskFreeSpace%])
SELECT DB_NAME() AS [DatabaseName], f.Name, f.physical_name, f.[file_id] As [FileID],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),f.size) * 8.0/1024.0,2)) As FileSizeMB,
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),f.size) * 8.0/1024.0,2) -
CONVERT(DECIMAL(18,2),FILEPROPERTY(f.name, ''SpaceUsed'') * 8.0/1024.0)) As FileFreeSpaceMB,
CONVERT(DECIMAL(18,2),CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS int)/128.0 AS DECIMAL(18,2)) / CAST((f.size/128.0) AS DECIMAL(18,3)) * 100) As [File Free Space(%)],
volume_mount_point [Disk Mount],
CONVERT(DECIMAL(18,2),total_bytes/1024.00/1024.00/1024.00) As [Disk Size(GB)],
CONVERT(DECIMAL(18,2),available_bytes/1024.00/1024.00/1024.00) AS [Disk Space Available(GB)],
CAST(CAST(available_bytes AS DECIMAL(18,2)) / CAST(total_bytes AS DECIMAL(18,2)) AS DECIMAL(18,3)) * 100 As [Disk Free Space %]
FROM sys.database_files f
INNER JOIN sys.master_files m on f.file_id = m.file_id
LEFT OUTER JOIN sys.data_spaces As ds WITH (NOLOCK) ON f.data_space_id = ds.data_space_id
CROSS APPLY sys.dm_os_volume_stats(database_id, m.file_id)
WHERE m.database_id = db_id()
'
Insert into [dbo].[LOG_SPACE_DB_DISK] ([DB],[LogicalFileName],[PhysicalFileName],[FileID],[FileSizeMB],[FileFreeSpaceMB],[FileFreeSpace%],
[DiskMount],[DiskSizeGB],[DiskFreeSpaceGB],[DiskFreeSpace%],[InsertedDate],[PeriodEnding])
Select
[DB],[LogicalFileName],[PhysicalFileName],[FileID],[FileSizeMB],[FileFreeSpaceMB],[FileFreeSpace%],
[DiskMount],[DiskSizeGB],[DiskFreeSpaceGB],[DiskFreeSpace%], GetDate(),@PeriodEnding
From #TMP order by [DB] asc, [FileID] asc
--Begin Warnings:
IF EXISTS(
Select 1 from [dbo].[LOG_SPACE_DB_DISK] where PeriodEnding = @PeriodEnding and ([FileFreeSpace%] > @AlertThresholdPercent and [FileFreeSpace%] < @WarningThresholdPercent)
or ([DiskFreeSpace%] > @AlertThresholdPercent and [DiskFreeSpace%] < @WarningThresholdPercent and [DiskFreeSpaceGB] < @WarningThresholdValue)
)
Begin
Set @IsWarnings = 1
IF EXISTS(
Select 1 from [dbo].[LOG_SPACE_DB_DISK] where PeriodEnding = @PeriodEnding and ([FileFreeSpace%] > @AlertThresholdPercent and [FileFreeSpace%] < @WarningThresholdPercent)
)
Begin
Select 'Warning[!] DB Space [Btw:' + Convert(varchar(5),@AlertThresholdPercent) + '%-' + Convert(varchar(5),@WarningThresholdPercent) + '%]' as 'Message',
[FileFreeSpace%], DB, [PhysicalFilename] from [dbo].[LOG_SPACE_DB_DISK] where PeriodEnding = @PeriodEnding
and ([FileFreeSpace%] > @AlertThresholdPercent and [FileFreeSpace%] < @WarningThresholdPercent) order by [FileFreeSpace%] asc
End
IF EXISTS(
Select 1 from [dbo].[LOG_SPACE_DB_DISK] where PeriodEnding = @PeriodEnding and ([DiskFreeSpace%] > @AlertThresholdPercent
and [DiskFreeSpace%] < @WarningThresholdPercent) and ([DiskFreeSpaceGB] < @WarningThresholdValue)
)
Begin
Select distinct 'Warning[!] Disk Space(' + Convert(varchar(5),@AlertThresholdPercent) + '%-' + Convert(varchar(5),@WarningThresholdPercent) + '%;<' + Convert(varchar(5),@WarningThresholdValue) + 'gb)' as 'Message',
[DiskFreeSpace%], [DiskFreeSpaceGB], DiskMount
from [dbo].[LOG_SPACE_DB_DISK] where PeriodEnding = @PeriodEnding and ([DiskFreeSpace%] > @AlertThresholdPercent and [DiskFreeSpace%] < @WarningThresholdPercent)
and ([DiskFreeSpaceGB] < @WarningThresholdValue)
order by [DiskFreeSpace%] asc
End
End
Else
Begin
Select 'No Warnings' as 'Message Warnings:'
End
--Begin Alerts:
IF EXISTS(
Select 1 from [dbo].[LOG_SPACE_DB_DISK] where PeriodEnding = @PeriodEnding and ([FileFreeSpace%] < @AlertThresholdPercent or [DiskFreeSpace%] < @AlertThresholdPercent
and [DiskFreeSpaceGB] < @WarningThresholdValue)
)
Begin
Set @IsAlerts = 1
IF EXISTS(
Select 1 from [dbo].[LOG_SPACE_DB_DISK] where PeriodEnding = @PeriodEnding and ([FileFreeSpace%] < @AlertThresholdPercent)
)
Begin
Select 'Alert[X] DB Space(<' + Convert(varchar(5),@AlertThresholdPercent) + '%)' as 'Message', [FileFreeSpace%], DB, [PhysicalFilename]
from [dbo].[LOG_SPACE_DB_DISK] where PeriodEnding = @PeriodEnding and ([FileFreeSpace%]) < @AlertThresholdPercent order by [FileFreeSpace%] asc
End
IF EXISTS(
Select 1 from [dbo].[LOG_SPACE_DB_DISK] where PeriodEnding = @PeriodEnding and ([DiskFreeSpace%] < @AlertThresholdPercent) and ([DiskFreeSpaceGB] < @AlertThresholdValue)
)
Begin
Select 'Alert[X] Disk Space(<' + Convert(varchar(5),@AlertThresholdPercent) + '%)' as 'Message', [DiskFreeSpace%], [DiskFreeSpaceGB], DiskMount
from [dbo].[LOG_SPACE_DB_DISK] where PeriodEnding = @PeriodEnding and ([DiskFreeSpace%] < @AlertThresholdPercent) order by [DiskFreeSpace%] asc
End
End
Else
Begin
Select 'No Alerts' as 'Message Alerts:'
End
--Begin Notifications:
If (@IsWarnings = 1 or @IsAlerts = 1) and @EmailReport = 1
Begin
SET @tableHTML =
N'<H4><Font Color=black><font face="Arial, Verdana, sans-serif;"><u>REPORT TITLE:</u> DATABASE FILE & DISK SPACE - WARNINGS & ALERTS</H4>' +
N'<H4><Font Color=black><font face="Arial, Verdana, sans-serif;">' + @@SERVERNAME + '</H4></font>' +
N'<H4><Font Color=black><font face="Arial, Verdana, sans-serif;">' + convert(nvarchar(25),@PeriodEnding) + '</H4></font>' +
N'<table border="1" style=”font-family:arial;color:black;font-size:11pt;”> ' +
N'<tr align="left"><th align="left" bgcolor="black" style="color:white;">Server</th><th align="left" bgcolor="black" style="color:white;">DB</th>
<th align="left" bgcolor="black" style="color:white;">Run Date</th> <th align="left" bgcolor="black" style="color:white;">FileFreeSpace%</th>
<th align="left" bgcolor="black" style="color:white;">PhysicalFileName</th><th align="left" bgcolor="black" style="color:white;">Disk Mount</th>
<th align="left" bgcolor="black" style="color:white;">DiskFreeSpace%</th>
</tr>' +
CAST ((
Select
Case
When [FileFreeSpace%] > @AlertThresholdPercent and [FileFreeSpace%] < @WarningThresholdPercent then '#ffff33'
When [FileFreeSpace%] < @AlertThresholdPercent then '#ff3333'
Else '#d9d9d9'
end AS [@bgcolor],
td = [Server], '',
td = [DB], '',
td = [PeriodEnding], '',
td = [FileFreeSpace%], '',
td = [PhysicalFileName], '',
td = [DiskMount], '',
td = [DiskFreeSpace%], ''
From
(
Select
@@ServerName as [Server],
[DB],
CONVERT(VARCHAR(12),@PeriodEnding,107) as [PeriodEnding],
[FileFreeSpace%],
[PhysicalFileName],
[DiskMount],
[DiskFreeSpace%]
from [dbo].[LOG_SPACE_DB_DISK]
where PeriodEnding = @PeriodEnding
) t
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
Set @tableHTML = '</br><font size=2>' + @tableHTML + '</br></br>' + '- Report Warning Run Values: File Space: Between ' + Convert(varchar(5),@AlertThresholdPercent) + '%-' + Convert(varchar(5),@WarningThresholdPercent) + '% free space'
Set @tableHTML = @tableHTML + '</br>' + '- Report Warning Run Values: Disk Space: Between ' + Convert(varchar(5),@AlertThresholdPercent) + '%-' + Convert(varchar(5),@WarningThresholdPercent) + '% free space and Less Than ' + Convert(varchar(5),@WarningThresholdValue) + 'GB free space'
Set @tableHTML = @tableHTML + '</br></br>' + '- Report Alert Run Values: File Space: Less Than ' + Convert(varchar(5),@AlertThresholdPercent) + '% free space'
Set @tableHTML = @tableHTML + '</br>' + '- Report Alert Run Values: Disk Space: Less Than ' + Convert(varchar(5),@AlertThresholdValue) + 'GB free space'
Set @tableHTML = @tableHTML + '</br></br>' + '<font size=2>- Source: sp_Server_Space_DB_DISK'
Set @tableHTML = @tableHTML + '</br></br>' + '- Delivered by DBMail on SRV-NYC-01' +
+
'</font>'
If @tableHTML <> '' and @tableHTML IS NOT NULL
Begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profilexyz',
@recipients='ops@xyz.ca',
@subject = 'MSSQL - Database File & Disk Space - Warnings & Alerts Report: SRV-NYC-01',
@body = @tableHTML,
@body_format = 'HTML' ;
End
End
Drop Table #TMP
GO
HTML Generated Report with row-level markers for Warnings and Alerts:
|
|
|
|
|
|
|
|
|
|