May 19, 2017 at 3:38 pm
Hi,
DBA's- do you have any manual or T-SQL scripts to email us if a drive is close to 30% of its free space and proactivevly alert us?
I know exec master..xp_fixeddrives but i cannot be running it on 50 plus prod servers and keep checking daily 24 x7 x365.
Thanks,
May 20, 2017 at 11:27 am
sqlguy80 - Friday, May 19, 2017 3:38 PMHi,
DBA's- do you have any manual or T-SQL scripts to email us if a drive is close to 30% of its free space and proactivevly alert us?I know exec master..xp_fixeddrives but i cannot be running it on 50 plus prod servers and keep checking daily 24 x7 x365.
Thanks,
Yes. The first step is to start monitoring your disk space daily. I wrote an article on this and it's published at http://www.sqlservercentral.com/articles/Drive+space/134523/. You could adapt it to check the space on the monitored drives right after inserting the rows for the day and send an email if you're below 30% free space. You will need to create a table of drives you want to monitor, but the beauty of it is that they could be the same on all your servers. The details are in the article.
There's also some stuff in there about using past growth history to estimate when you'll run out of space if the growth stays linear.
May 22, 2017 at 2:31 am
This was removed by the editor as SPAM
May 23, 2017 at 1:44 am
sqlguy80 - Friday, May 19, 2017 3:38 PMHi,
DBA's- do you have any manual or T-SQL scripts to email us if a drive is close to 30% of its free space and proactivevly alert us?I know exec master..xp_fixeddrives but i cannot be running it on 50 plus prod servers and keep checking daily 24 x7 x365.
Thanks,
Use the script shared above by others and configure database mail, use the same in the job where you calling the script.
May 23, 2017 at 7:03 am
I use a WMI event Alert in the SQL Agent to monitor the free space of my drives and alert me if they fall below a set threshold. You can't do a percentage, and you need one alert per drive in the server you want to monitor, but it's not a lot to set up once you've done it once.USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'SERVERNAME - Drive C < 2.5GB',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@notification_message=N'Drive C: on SERVERNAME is below 2.5GB free. This is the OS Drive',
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\CIMV2',
@wmi_query=N'select * from __instancemodificationevent within 1 where targetinstance isa ''CIM_LogicalDisk'' and targetinstance.freespace < 2500000000 and targetinstance.name = ''C:''',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'SERVERNAME - Drive C < 2.5GB', @operator_name=N'SQLAgent Operator', @notification_method = 1
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply