ways to receive space alert

Question

Post reply

ways to receive space alert

atulyan.aries

SSCrazy

Points: 2018
More actions
August 27, 2015 at 5:01 am

#299950

Hi Team,
I'm tasked with the process to setup database mail which generate space threshold alerts.
A script will run at periodic intervals, once the threshold is breached an alert ( email will be generated to the recepients mail id)
The question, is there any other way this alert (email) can be generated.
Regards
Atulyan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

Exercise your Google-fu. Found a bunch of hits in less than a second.

sys.dm_os_volume_stats - see example A.

An example from SQLMag...

Sounds like you might need a report or something that tracks free space over time so that you know roughly when the drive will run out of space given the current growth rate(s). You could just write the results of dm_os_volume_stats to a table with a date. Then you could just show that as a graph...

Ed Wagner SSC Guru Points: 287024 More actions · Answer 2

Continuing on pietlinden's approach, first determine your minimum threshold per volume and your tracking requirements - once a day, once every 6 hours, once an hour, etc.

Your checker procedure fires periodically (as a database job) and determines drive, capacity and free space. If free space is below the threshold, use database mail to fire off an email to yourself. Depending on your tracking requirements, the procedure might also write the data to a table for tracking. Once you have some data in there, you can graph it by drive and hopefully have a good idea when you're going to run out of space. This moves you from being reactive to planning ahead of time.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Don't do this in SQL Server. Do it in Windows. Please see the following KB.

https://support.microsoft.com/en-us/kb/324796

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)