Είναι πραγματικά άδικο, σαν DBA, να ξεκινάει η μέρα σου με προβλήματα που αφορούν την χωρητικότητα των volumes που περιέχουν τα αρχεία των database που έχεις σε ένα SQL Server instance.

Είναι άδικο, καθώς εδώ και πολλά χρόνια μπορούμε να κάνουμε εύκολα monitor τον ελεύθερο χώρο που έχουμε στα volumes που χρησιμοποιούνται από το SQL Server instance μας και να είμαστε σε θέση να δρούμε πριν αυτά γεμίσουν.

Solution

Όλα αυτά τα χρόνια έχω δει αρκετές λύσεις που προσπαθούν να αντιμετωπίσουν το συγκεκριμένο θέμα. Κάποιες από αυτές χρησιμοποιούν WMI κάποιες άλλες χρησιμοποιούν PowerShell.

Πραγματικά δεν έχω λόγια που να μπορούν να περιγράψουν αυτές τις τεχνικές καθώς υπάρχει καλύτερη, καθαρότερη και ευκολότερη στην υλοποίηση λύση που δεν απαιτεί τίποτα περισσότερο από SQL Server και αυτή είναι η παρακάτω

DECLARE @SizeThreshold DECIMAL(18,2) = 25.0; -- change it with your value(%)
DECLARE @msg VARCHAR(8000) ='';
DECLARE @mailme bit = 0;
WITH Volumes
AS
(
    SELECT    DISTINCT 
            UPPER(volume_mount_point) AS Volume, 
            file_system_type AS VolumeSystemType, 
            logical_volume_name AS VolumeName, 
            CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS TotalSizeGB, 
            CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS FreeSizeGB,  
            CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS SpaceFreePct 
    FROM sys.master_files 
    CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)
)
SELECT @msg+= 'Volume ' + Volume +' ('+VolumeName+') has ' + CAST(SpaceFreePct as varchar(20)) +'% free space' + CHAR(10)
FROM Volumes
WHERE SpaceFreePct<@SizeThreshold;

IF LEN(@msg) > 0   SET @mailme = 1

IF @mailme = 1 
BEGIN
    SET @msg = 'Volumes with low free space are:'+CHAR(10)+CHAR(13) + @msg;
    DECLARE @subject nvarchar(255) = 'Volumes with low free space in ' + @@servername + ' SQL Server instance'   ;
    EXEC msdb.dbo.sp_send_dbmail 
                 @profile_name   =  '[database mail profile]'   
            ,    @recipients     =  '[recipients list]'   
            ,    @subject        =  @subject
            ,    @body           =  @msg
            ,    @body_format    =  'TEXT'   
            ,    @importance     =  'HIGH'   ;
END;

How it works

Η συγκεκριμένη λύση βασίζεται σε δύο γνωστές DMVs τις sys.master_files και sys.dm_os_volume_stats.

Η πρώτη επιστρέφει όλα τα αρχεία όλων των databases (συμπεριλαμβανόμενων και των system databases) που το SQL Server instance μας έχει.

Η δεύτερη επιστρέφει πληροφορίες για το OS volume στο οποίο ένα συγκεκριμένο αρχείο μιας database ανήκει.

Και οι δύο υπάρχουν στον SQL Server από την έκδοση 2008!

Το μόνο που χρειάζεται να κάνει κάποιος είναι απλά να κάνει ένα join query μεταξύ τους απλά επειδή η δεύτερη είναι function θα πρέπει να χρησιμοποιηθεί ο CROSS APLLY operator και με την DISTINCT να κάνει deduplication.

Οι υπολογισμοί της χωρητικότητας είναι απλοί όπως βλέπετε και γενικότερα το συγκεκριμένο query είναι απλό.

Για την ευκολία μας το βάζω σε ένα CTE στο οποίο απλά λαμβάνω μόνο αυτά που έχουν μέγεθος λιγότερο από το threshold που θέλω και που το ορίζω στην @SizeThreshold variable.

Στην συνέχεια απλά "χτίζω" ένα string το οποίο περιέχει το body του ενημερωτικού email που θα αποσταλθεί στους DBA και με την γνωστή sp_send_dbmail (αφού φυσικά έχω ενεργοποιημένο το database mail).

How to use it

Αυτό όλο το script αφού ορίσω τις παραμέτρους που θέλω για την αποστολή του email και για το threshold το κάνω ένα απλό SQL job και το βάζω να τρέχει πχ κάθε 15 λεπτά (ο χρόνος είναι υποκειμενικός). Απλά και εύκολα όταν θα έχω σε κάποιο volume ελεύθερο χώρο κάτω από το threshold που έχω ορίσει θα λάβω ένα email που θα μου λέει τα εξής:

Volumes with low free space are:

Volume D:\ (DATA1) has 4.00% free space
Volume L:\ (LOGS1) has 22.00% free space

//Antonios Chatzipavlis