Sql-server – How to configure DB server space alert step by step

alertssql serversql-server-2016

Could you please help me on the question.

Here is the requirement.

I have one server and there is 2 drives are available (C and D).

Due to mssql Log file growth E drive is always getting filled and application is used to down.

Can some one help me with MSSQL procedure to check the drive and send me an alert while Disk space used 70%.

Best Answer

Hopefully as Jacob mentions you are taking log backups. Here is a script which you could run as a Sql Server job which emails you the current free space on your instance drives. You could tweak it with logic if over 70%. Hope this helps.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourMailProfileName',
@recipients = 'YourEmailName',
@subject = 'Current free space on drives',
@query = N'
SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC;
GO',
@attach_query_result_as_file = 0
--@query_attachment_filename = 'CurrentFreeSpaceOnDrive.txt'