How to send Disk space report daily by a Job
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
Enabling xp_cmdshell & database Mail XPs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
sp_configure 'show advanced options' ,1 RECONFIGURE GO sp_configure 'xp_cmdshell' ,1 RECONFIGURE GO sp_CONFIGURE 'Database Mail XPs' ,1 RECONFIGURE GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
-- 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 into 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 |
TSQL Script is collected from internet, 100% working
Download TSQL: DISK_SPACE_REPORT_VIA_EMAIL
Result
Facebook Comments Box
Comments
Hello Sanjay,
I have created agent job for daily Disk space report using this script. The job was running fine but now the job is failing with error message. Could you help me how to solve this problem?
Configuration option ‘show advanced options’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘xp_cmdshell’ changed from 1 to 1. Run the RECONFIGURE statement to install.
Database name ‘Tempdb’ ignored, referencing object in tempdb.
Database name ‘Tempdb’ ignored, referencing object in tempdb.
Database name ‘Tempdb’ ignored, referencing object in tempdb.
Database name ‘Tempdb’ ignored, referencing object in tempdb.
Database name ‘Tempdb’ ignored, referencing object in tempdb.
Database name ‘Tempdb’ ignored, referencing object in tempdb.
Database name ‘Tempdb’ ignored, referencing object in tempdb.
Database name ‘Tempdb’ ignored, referencing object in tempdb.
Msg 8134, Level 16, State 1, Line 74
Divide by zero error encountered.
Mail (Id: 5504) queued.
Completion time: 2022-06-01T08:57:44.3743293-04:00