1.
Background
The basic aim of this article is to describe a way of
sending email notification whenever insufficient disk space found in any of the
drive of the server/computer. This notification is useful while supporting and
maintenance of servers. Here we are using xp_fixeddrives system stored procedure for getting the disk
space information from the server/computer and Database Mail for sending
notification. For configuring the Database mail, you can refer the below link:
sending email notification whenever insufficient disk space found in any of the
drive of the server/computer. This notification is useful while supporting and
maintenance of servers. Here we are using xp_fixeddrives system stored procedure for getting the disk
space information from the server/computer and Database Mail for sending
notification. For configuring the Database mail, you can refer the below link:
2.
Pre-requisite
1.
Database Mail configuration should be configured
for sending email notification.
Database Mail configuration should be configured
for sending email notification.
2.
SQL Server Agent service should run.
SQL Server Agent service should run.
3.
What is xp_fixeddrives?
xp_fixeddrives is
an extended stored procedure that provides free space availability details of
all the disk drives available in server/computer. This stored procedure returns
amount of free space available in MB.
an extended stored procedure that provides free space availability details of
all the disk drives available in server/computer. This stored procedure returns
amount of free space available in MB.
4. Step by Step procedure to send Insufficient Disk Space Notification:
i. We have created a stored procedure named “GetServerSpaceStatus” that will return
the disk drive name and its available free space in MB if any of the disk space
is equal or less than 100 MB.
We have
set the criteria for insufficient disk space is equal or less than 100 MB.
Below is the stored procedure that uses xp_fixeddrives extended stored
procedure with filter condition of 100 MB or less free space of disk drive.
set the criteria for insufficient disk space is equal or less than 100 MB.
Below is the stored procedure that uses xp_fixeddrives extended stored
procedure with filter condition of 100 MB or less free space of disk drive.
CHANGE
HISTORY
HISTORY
********************************************************
Date: Author: Description: (CR#, Ver, Bug# etc)
———– ———– ——————-
21-Oct-20 Vishal Jharwade 1. The purpose of the SP is to find the drive
name which is
having insufficient disk space.
having insufficient disk space.
2. Criteria
for insufficient disk space- 100 MB
for insufficient disk space- 100 MB
********************************************************/
AS
BEGIN
SET NOCOUNT ON
DECLARE @sErrorMessage AS NVARCHAR(255)
DECLARE @lErrorMessageID AS INT
DECLARE @lReturnCode AS INT
DECLARE @sMessage AS
NVARCHAR(4000)
NVARCHAR(4000)
DECLARE @lIdentity AS
INT
INT
DECLARE @DiskDrive AS
NVARCHAR(100)
NVARCHAR(100)
DECLARE @DiskSpace AS
INT
INT
DECLARE @SUBJECTMESSAGE NVARCHAR(500)
DECLARE @tableHTML NVARCHAR(500)
BEGIN TRY
–Declaring
table variable for storing Disk space information
table variable for storing Disk space information
DECLARE @DiskFreeSpace AS TABLE
(
Drive CHAR(1),
MB_Free INT
)
–Inserting
disk space availability details into table variable
disk space availability details into table variable
INSERT INTO @DiskFreeSpace
EXEC xp_fixeddrives
–Storing drive name and free space(in MB) in
variable.
variable.
–Creteria for insufficient disk space equal or less
than 100 MB
than 100 MB
SELECT @DiskDrive= Drive ,
@DiskSpace
= MB_Free
= MB_Free
FROM @DiskFreeSpace
WHERE MB_Free < 100
SET @SUBJECTMESSAGE= ‘Production Support Mail: Insufficent Disk Space in ‘ +
@DiskDrive + ‘ Drive in
SQLCircuit Blogspot Server’
SQLCircuit Blogspot Server’
SET @tableHTML = ‘This is to notify you that Insufficient disk space
encountered
encountered
in’ + @DiskDrive + ‘ Drive’ + ‘(‘+ + ‘)’ + ‘in SQLCircuit
Blogspot
Server, Please take necessary action to avoid any further issues’ +
Server, Please take necessary action to avoid any further issues’ +
CHAR(4) + ‘Thanks,’ + CHAR(4) +‘SQLCircuit Team‘
—Sending Mail
EXEC msdb.dbo.sp_send_dbmail
@recipients=‘sqlcircuit@gmail.com’,
@subject
= @SUBJECTMESSAGE,
@Profile_Name=‘sqlcircuit’, –Profile of SMTP Server
@body
= @tableHTML,
@body_format
= ‘HTML’ ;
SET @lReturnCode = 0
END TRY
BEGIN CATCH
–logging Error information
INSERT INTO [dbo].[ErrorLog]
([MessageID],[ErrorNumber],[ErrorSeverity],[ErrorState],
[ErrorProcedure],[ErrorLine],[ErrorMessage],[CreatedBy],
[CreatedDate])
VALUES
(1,ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ISNULL(ERROR_PROCEDURE(),
‘-
‘),ERROR_LINE(),ERROR_MESSAGE(),”,GETDATE())
END CATCH
RETURN @lReturnCode
SET NOCOUNT OFF
END
ii.
Execute the stored procedure and check the
email whether we have received mail or not.
SP
Execution:
Execution:
the email to confirm whether we have received email or not: Below screenshot is
showing that we have received Insufficient Disk Space notification:
5.
How to use this approach
in practical scenarios:
We can create a SQL Agent Job that
should run continuously or with minimum time interval (1 or 2 mins). In this job,
we can call the stored procedure GetServerSpaceStatus
that will return Disk drive name with insufficient disk space.
should run continuously or with minimum time interval (1 or 2 mins). In this job,
we can call the stored procedure GetServerSpaceStatus
that will return Disk drive name with insufficient disk space.
6.
Conclusion
By using
the above steps, we can implement Insufficient Disk Space Notification.
the above steps, we can implement Insufficient Disk Space Notification.
—————————————————-End
of Document—————————————————
of Document—————————————————