How do I create a disk space alert in SQL Server? | How to solve disk space issue in SQL Server?

This Article guides,

How do I create a disk space alert in SQL Server? or How to solve disk space issue in SQL Server?

Many time we face disk space issue on SQL Server on Azure managed instance. If we configured alert for low disk space this can help to avoid database getting down.

SQL Server and Azure Managed instance both support database mail configuration. Using Database mail, we can configure alert for low disk space. To configure database mail check below step number 1.

Azure SQL Managed Instance is pass offering from Microsoft which have all the capabilities of SQL server VM along with SQL agents support.

This is same as Azure SQL Database i.e. you do not have any control over VM or hardware configuration.

But in background it runs on VM and we have to keep eye on disk space utilization of azure SQL managed instance to ensure that it do not get full and we are in situation of database crash.

Below query can help to configure email alert for any disk space crunch on azure SQL managed instance. We can specify percentage as per your requirement in query and configure database mail on SQL instance to get email alert.

This can be done by using SQL Agent job as azure SQL managed instnace support SQL Agent feature which is great advantage.

Follow below step to configure Disk space alert SQL Server or Azure managed instance.

Step 1. Configure database mail Click here to to find step by step guide

Step 2. Use below query to create SQL job in azure SQL managed instance.

DECLARE @emailBody VARCHAR(MAX)
DECLARE @emailSubject VARCHAR(MAX)
DECLARE @serverName VARCHAR(MAX)
SET @emailSubject = 'PROD MI - Low disk Space Alert!'
SET @serverName = @@serverName
CREATE table #drivespace(volume_mount_point varchar(max),used_gb float,available_gb float,total_gb float)
INSERT INTO #drivespace
SELECT volume_mount_point,
used_gb = CAST(MIN(total_bytes / 1024. / 1024 / 1024) AS NUMERIC(8,1)),
available_gb = CAST(MIN(available_bytes / 1024. / 1024 / 1024) AS NUMERIC(8,1)),
total_gb = CAST(MIN((total_bytes+available_bytes) / 1024. / 1024 / 1024) AS NUMERIC(8,1))
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
GROUP BY volume_mount_point;

DECLARE @FreeDiskSpacePercent int
DECLARE @DriveName Varchar(100)
select @DriveName = volume_mount_point,@FreeDiskSpacePercent = ((total_gb-used_gb)/total_gb)*100 from #drivespace WHERE volume_mount_point = 'http://'

IF @FreeDiskSpacePercent <=10
BEGIN
SET @emailBody = CHAR(13) + CHAR(13) + 'Free Disk space on drive(s) '+ @DriveName+' on ' + @serverName + ' is less 10%'
--- EMAIL REPORT
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'add email ID'
, @body = @emailBody
, @subject = @emailSubject
, @importance = 'HIGH'
END
DROP TABLE #drivespace

Step 3 : Create SQL job and schedule it as per your requirement. To know how we can schedule SQL job on azure SQL managed instance check this.

How to solve disk space issue in SQL Server?

There are many reasons of disk space issue on SQL Server. Below are some common issues cause low disk space.

1. Log file of database increase and occupied all the disk space. Check this to resolve log file issue
2. Backup files not getting deleted. Check backup jobs and add cleanup task to delete old backup files.
3. Long running transaction causing temp DB full. To resolve Temp DB issue check this.
4. Sometime Database size grown and taking full drive space. Identify database growth and add extra disk space on drive or archive old data.
5. For Azure Managed instance if we get alert, you can create support ticket with Microsoft and take suggestion as this is pass offering and we do not have control over.