Disk Space Report

CREATE PROCEDURE [dbo].[usp_DiskSpaceUsage]
(@thresholdMB int = 20000, @thresholdPct tinyint = 20)
AS
DECLARE
 @svrname VARCHAR(255);
DECLARE
 @sql VARCHAR(400);
--by default it will take the current server name, we can the set the server name as well
SET @svrname = CONVERT(VARCHAR(128), SERVERPROPERTY('machinename'));-- @@SERVERNAME
CREATE TABLE #output
(
  line VARCHAR(255)
);
SET NOCOUNT ON;
DECLARE
 @version NUMERIC(18, 10);
SET @version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)))-1)+'.'+REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)))-CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(MAX)))), '.', '') AS NUMERIC(18, 10));
IF EXISTS
(
   SELECT *
   FROM   sys.configurations
   WHERE  name LIKE 'xp_cmdshell'
AND value_in_use = 1
)
   BEGIN
  SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName '+QUOTENAME(@svrname, '''')+' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"';
  --creating a temporary table
  --inserting disk name, total space and free space value in to temporary table
  INSERT INTO #output
  EXEC xp_cmdshell
  @sql;

  --script to retrieve the values in GB from PS Script output
  INSERT INTO DBATasks.dbo.[DiskGrowthSnapDetail]
  (DriveName
 , Capacity
 , FreeSpace
  )
  SELECT RTRIM(LTRIM(SUBSTRING(line, 1, CHARINDEX('|', line)-1))) AS                                                                      drivename
  , ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('|', line)+1, (CHARINDEX('%', line)-1)-CHARINDEX('|', line)))) AS FLOAT), 0) AS 'capacity(GB)'
  , ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('%', line)+1, (CHARINDEX('*', line)-1)-CHARINDEX('%', line)))) AS FLOAT), 0) AS 'freespace(GB)'
  FROM   #output
  WHERE  line LIKE '[A-Z][:]%'
  ORDER BY drivename;
  --script to drop the temporary table
   END;
ELSE
   BEGIN
  EXEC sp_configure
  'show advanced options'
, 1;
  RECONFIGURE WITH OVERRIDE;
  EXEC sp_configure
  'xp_cmdshell'
, 1;
  RECONFIGURE WITH OVERRIDE;
  SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName '+QUOTENAME(@svrname, '''')+' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"';
  --creating a temporary table
  --inserting disk name, total space and free space value in to temporary table
  INSERT INTO #output
  EXEC xp_cmdshell
  @sql;

  --script to retrieve the values in GB from PS Script output
  INSERT INTO DBATasks.dbo.[DiskGrowthSnapDetail]
  (DriveName
 , Capacity
 , FreeSpace
  )
  SELECT RTRIM(LTRIM(SUBSTRING(line, 1, CHARINDEX('|', line)-1))) AS                                                                      drivename
  , ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('|', line)+1, (CHARINDEX('%', line)-1)-CHARINDEX('|', line)))) AS FLOAT), 0) AS 'capacity(GB)'
  , ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line, CHARINDEX('%', line)+1, (CHARINDEX('*', line)-1)-CHARINDEX('%', line)))) AS FLOAT), 0) AS 'freespace(GB)'
  FROM   #output
  WHERE  line LIKE '[A-Z][:]%'
  ORDER BY drivename;
  --script to drop the temporary table
  EXEC sp_configure
  'xp_cmdshell'
, 0;
  RECONFIGURE WITH OVERRIDE;
  EXEC sp_configure
  'show advanced option'
, 0;
  RECONFIGURE WITH OVERRIDE;
   END;
DELETE DBATasks..DiskGrowthSnapDetail
WHERE  ReportDate < DATEADD(DD, -365, GETDATE());
DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Disk Space Report for ' + @@SERVERNAME + ' </H1>' +
    N'<table border="1">' +
    N'<tr><th>Drive Letter</th><th>Total Space</th>' +
    N'<th>Free Space</th><th>Percent Free</th><th>Report Date</th>' +
    CAST ( ( SELECT td = DriveName,       '',
                    td = Capacity, '',
                    td = FreeSpace, '',
                    td = CONVERT( DECIMAL(20, 2), CONVERT(FLOAT, Freespace) / (Capacity) * 100), '',
                    td = ReportDate
              FROM  DBATasks..[DiskGrowthSnapDetail]
WHERE ReportDate IN (SELECT MAX(ReportDate) FROM DBATasks..DiskGrowthSnapDetail)
AND FreeSpace < @thresholdMB and CONVERT( DECIMAL(20, 2), CONVERT(FLOAT, Freespace) / (Capacity) * 100) < @thresholdPct
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

 if exists (select 1
    FROM  DBATasks..[DiskGrowthSnapDetail]
WHERE ReportDate IN (SELECT MAX(ReportDate) FROM DBATasks..DiskGrowthSnapDetail)
AND FreeSpace < @thresholdMB and CONVERT( DECIMAL(20, 2), CONVERT(FLOAT, Freespace) / (Capacity) * 100) < @thresholdPct)
EXEC msdb.dbo.sp_send_dbmail @recipients='DBA-ICD-SQLServer@mycompany.com',
    @subject = 'Disk Space Report',
    @body = @tableHTML,
@from_address = 'DBMail-Prod@mycompany.com',
    @body_format = 'HTML' ;