sqlschool.gr logo

articles

Articles of SQLschool.gr Team

Monitoring Disk Free Space for all SQL Server volumes

Antonios Chatzipavlis
Thursday 08 October 2020

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

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

Solution

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

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

SQL Script

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 in [your instance] SQL Server instance

Volumes with low free space are:

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

//Antonios Chatzipavlis


Comments

14 Oct 2020 @ 3:27 PM

user-gravatar

Christos M

καλησπερα Αντωνη..Δουλευει σε ολα τα versions?

15 Oct 2020 @ 11:01 AM

user-gravatar

Antonios Chatzipavlis

Δουλεύει σε όσα είναι διαθέσιμες οι sys.master_files και sys.dm_os_volume_stats.

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.