Table of Contents

Scenario

Send Disk space report daily by a Job. Here we will use a TSQL script for the activity. The script will create two temporary tables for the activity and use the Database mail to send the report via email.

Steps

1) First Configure Database mail in SQL Server if it is not configured before. You can follow the link for instruction: https://www.ourtechideas.com/blog/configure-database-mail/

2) Create a Job for the activity & schedule as per your requirement

3) Modify the script & use it in the Job

Must modify in the script
- Email Sender Name
- DB Mail Profile
- Recipients Email
- Copy Recipients Email

TSQL Script

-- =========================================
-- Enableing xp_cmdshell & atabase Mail XPs
-- =========================================
sp_configure 'show advanced options',1
RECONFIGURE
go

sp_configure 'xp_cmdshell',1
RECONFIGURE
GO

sp_CONFIGURE 'Database Mail XPs', 1
RECONFIGURE
GO
-- =========================================
-- Deleting temporary tables
-- =========================================
SET NOCOUNT ON 
 IF OBJECT_ID('Tempdb..#Creport') IS NOT NULL  DROP TABLE Tempdb..#Creport;
 IF OBJECT_ID('Tempdb..#output') IS NOT NULL  DROP TABLE Tempdb..#output;

-- ========================================================================================
-- by default it will take the current server name, we can set the server name as well
-- ========================================================================================
DECLARE @sql varchar(4000)
DECLARE @LOCAL_NET_ADDRESS varchar(20)
DECLARE @svrName varchar(20)
set @svrName = CAST (SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as varchar(30))
Set @LOCAL_NET_ADDRESS =  Cast (CONNECTIONPROPERTY('LOCAL_NET_ADDRESS') as varchar (255))
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
-- ===========================
CREATE TABLE Tempdb..#output
(line varchar(255))

CREATE TABLE Tempdb..#Creport
(
[SERVER NAME] varchar (100),
DRIVENAME varchar (100),
CAPACITY_GB INT,
FREESPACE_GB INT,
USED_SPACE_PERCENTAGE AS (100-(FREESPACE_GB * 100/ CAPACITY_GB))
)

insert #output
EXEC xp_cmdshell @sql

--script to retrieve the values in MB from PS Script output
--script to retrieve the values in GB from PS Script output

-- ===========================================================================
-- inserting disk name, total space and free space value in to temporary table
-- ===========================================================================

INSERT INTO Tempdb..#Creport
SELECT --CONNECTIONPROPERTY('LOCAL_NET_ADDRESS') AS LOCAL_NET_ADDRESS, 
@SVRNAME[SERVER NAME],
 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)/1024,0) AS 'CAPACITY(GB)'
   ,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(LINE,CHARINDEX('%',LINE)+1,(CHARINDEX('*',LINE) -1)-CHARINDEX('%',LINE)) )) AS FLOAT) /1024 ,0)AS 'FREESPACE(GB)'
from Tempdb..#output
where line like '[A-Z][:]%'
order by drivename

-- =================================
-- HTML
-- =================================

DECLARE @tableHTML  NVARCHAR(MAX) ;
declare @servername varchar(max)
set @servername=@@SERVERNAME

SET @tableHTML =
  
    N'<H3> Disk space available on: ' + @svrName + '</H3>'+-- ' | ' + @LOCAL_NET_ADDRESS + 
    N'<table border="1" Cellspacing="0" cellpadding="0" style="font-size:x-small;">' +
    N'<tr style="background-color:#FFFF00;color:black;font-weight:bold;" align="center">' +	
	N'<td align=center><b>SERVER NAME</b></td>
	<td align=center><b>DRIVENAME</b></td>
	<td align=center><b>CAPACITY(GB)</b></td>
	<td align=center><b>FREESPACE(GB)</b></td>
	<td align=center><b>USED_SPACE_PERCENTAGE</b></td>'+

    CAST ( ( SELECT 
					td= [SERVER NAME],'',
					td= [DRIVENAME],'',
					td= [CAPACITY_GB],'',
					td= [FREESPACE_GB],'',
					td= [USED_SPACE_PERCENTAGE],''
               FROM #Creport			  
			   FOR XML PATH('tr'), TYPE 
	) AS NVARCHAR(MAX) )+
    N'</center></table>' + N'<BR><BR><BR>Thanks<BR> Sanjay Humania <BR><BR>' ; ; -- Sender's Name

if (select count(*) FROM #Creport) > 0  
Begin
DECLARE @today varchar(20) = convert(varchar, getdate(), 105) 
DECLARE @subject Nvarchar(max)
DECLARE @sqlstr varchar (400)
set @sqlstr ='Disk space available on: '+ @svrName --+ ' | ' + @LOCAL_NET_ADDRESS --+' | ' + @today

-- ==========================================
-- Email server configuration
-- ==========================================

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'SQL_DB_Email', -- Change Profile Name
@recipients = 'sanjay.humania@gmail.com;', -- Change recipients name
@copy_recipients = 'sanjayhumania@gmail.com;',  -- Change copy recipients name
@subject = @sqlstr,
@body = @tableHTML,
@body_format = 'HTML' ;
End 
  
-- ===================================
-- Deleting temp tables
-- ===================================

Drop table [#Creport]
go

sp_configure 'xp_cmdshell',0
go
RECONFIGURE
-- =======
-- END
-- =======

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

-- =========================================

-- Enableing xp_cmdshell & atabase Mail XPs

-- =========================================

sp_configure 'show advanced options',1

RECONFIGURE

sp_configure 'xp_cmdshell',1

RECONFIGURE

sp_CONFIGURE 'Database Mail XPs', 1

RECONFIGURE

-- =========================================

-- Deleting temporary tables

-- =========================================

SET NOCOUNT ON

IF OBJECT_ID('Tempdb..#Creport') IS NOT NULL DROP TABLE Tempdb..#Creport;

IF OBJECT_ID('Tempdb..#output') IS NOT NULL DROP TABLE Tempdb..#output;

-- ========================================================================================

-- by default it will take the current server name, we can set the server name as well

-- ========================================================================================

DECLARE @sql varchar(4000)

DECLARE @LOCAL_NET_ADDRESS varchar(20)

DECLARE @svrName varchar(20)

set @svrName = CAST (SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as varchar(30))

Set @LOCAL_NET_ADDRESS = Cast (CONNECTIONPROPERTY('LOCAL_NET_ADDRESS') as varchar (255))

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

-- ===========================

CREATE TABLE Tempdb..#output

(line varchar(255))

CREATE TABLE Tempdb..#Creport

(

[SERVER NAME] varchar (100),

DRIVENAME varchar (100),

CAPACITY_GB INT,

FREESPACE_GB INT,

USED_SPACE_PERCENTAGE AS (100-(FREESPACE_GB * 100/ CAPACITY_GB))

)

insert #output

EXEC xp_cmdshell @sql

--script to retrieve the values in MB from PS Script output

--script to retrieve the values in GB from PS Script output

-- ===========================================================================

-- inserting disk name, total space and free space value in to temporary table

-- ===========================================================================

INSERT INTO Tempdb..#Creport

SELECT --CONNECTIONPROPERTY('LOCAL_NET_ADDRESS') AS LOCAL_NET_ADDRESS,

@SVRNAME[SERVER NAME],

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)/1024,0) AS 'CAPACITY(GB)'

,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(LINE,CHARINDEX('%',LINE)+1,(CHARINDEX('*',LINE) -1)-CHARINDEX('%',LINE)) )) AS FLOAT) /1024 ,0)AS 'FREESPACE(GB)'

from Tempdb..#output

where line like '[A-Z][:]%'

order by drivename

-- =================================

-- HTML

-- =================================

DECLARE @tableHTML NVARCHAR(MAX) ;

declare @servername varchar(max)

set @servername=@@SERVERNAME

SET @tableHTML =

N'<H3> Disk space available on: ' + @svrName + '</H3>'+-- ' | ' + @LOCAL_NET_ADDRESS +

N'<table border="1" Cellspacing="0" cellpadding="0" style="font-size:x-small;">' +

N'<tr style="background-color:#FFFF00;color:black;font-weight:bold;" align="center">' +

N'<td align=center><b>SERVER NAME</b></td>

<td align=center><b>DRIVENAME</b></td>

<td align=center><b>CAPACITY(GB)</b></td>

<td align=center><b>FREESPACE(GB)</b></td>

<td align=center><b>USED_SPACE_PERCENTAGE</b></td>'+

CAST ( ( SELECT

td= [SERVER NAME],'',

td= [DRIVENAME],'',

td= [CAPACITY_GB],'',

td= [FREESPACE_GB],'',

td= [USED_SPACE_PERCENTAGE],''

FROM #Creport

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) )+

N'</center></table>' + N'<BR><BR><BR>Thanks<BR> Sanjay Humania <BR><BR>' ; ; -- Sender's Name

if (select count(*) FROM #Creport) > 0

Begin

DECLARE @today varchar(20) = convert(varchar, getdate(), 105)

DECLARE @subject Nvarchar(max)

DECLARE @sqlstr varchar (400)

set @sqlstr ='Disk space available on: '+ @svrName --+ ' | ' + @LOCAL_NET_ADDRESS --+' | ' + @today

-- ==========================================

-- Email server configuration

-- ==========================================

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQL_DB_Email', -- Change Profile Name

@recipients = 'sanjay.humania@gmail.com;', -- Change recipients name

@copy_recipients = 'sanjayhumania@gmail.com;', -- Change copy recipients name

@subject = @sqlstr,

@body = @tableHTML,

@body_format = 'HTML' ;

End

-- ===================================

-- Deleting temp tables

-- ===================================

Drop table [#Creport]

sp_configure 'xp_cmdshell',0

RECONFIGURE

-- =======

-- END

-- =======

TSQL Script is collected from internet, 100% working

Download TSQL: DISK_SPACE_REPORT_VIA_EMAIL

Scenario

Steps

TSQL Script

Result

How to send Database backup report daily by a Job

ETA of Backup and Restoration for SQL Server Databases

Comments

Categories

Recent Posts

Recent Comments

Archives

Related Posts