go backarticles

Articles of SQLschool.gr Team

Monitoring Disk Free Space for all SQL Server volumes

Antonios Chatzipavlis

Είναι πραγματικά άδικο, σαν 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



Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Comments

user-gravatar

On 14 Oct 2020 @ 3:27 PM Christos M wrote:

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

user-gravatar

On 15 Oct 2020 @ 11:01 AM Antonios Chatzipavlis wrote:

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

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

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