Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.436 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • March 4th, 2017
  • 0
  • Database DMVs and Catalog Views Maintenance Monitoring SQL Server

SQL Server - How to identify and monitor total free disk space used by database datafiles

Views: 6.133
Reading Time: 5 minutes

Hello people,
How are you ?

In this post, I will demonstrate how to identify and monitor the total, free, and used disk space of your instance databases. This post is a complement to my post. SQL Server - How to identify and monitor disks, free and used total disk spacewhere I demonstrated how to identify, analyze and monitor disk space on disk drives.

How to identify the total, free and used space of databases

For this need, I will use the views:

  • sys.master_files: Datafiles Information for Your Databases
  • sys.databases: Database Information
  • sys.dm_os_volume_stats: DMV that shows information about the physical disks of a datafile

To create the script below, I decided to make some validations on the view information, since some situations end up generating inaccurate and incorrect information in their monitoring. These validations are:

  • If the maximum datafile size is larger than the maximum disk size, the actual maximum datafile size is the maximum disk size and not the datafile size.
  • If the maximum size of the datafile is set to “Unlimited”, the actual maximum size of the datafile will be the maximum size of the disk and the free space of the datafile will be the free space of the disk on which the datafile is.
  • If datafile growth is set to percent, some columns will not be calculated, leaving NULL (growth_times)

View source

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
IF (OBJECT_ID('tempdb..#Datafile_Size ') IS NOT NULL) DROP TABLE #Datafile_Size
SELECT
    B.database_id AS database_id,
    B.[name] AS [database_name],
    A.state_desc,
    A.[type_desc],
    A.[file_id],
    A.[name],
    A.physical_name,
    CAST(C.total_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_total_size_GB,
    CAST(C.available_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_free_size_GB,
    CAST(A.size / 128 / 1024.0 AS NUMERIC(18, 2)) AS size_GB,
    CAST(A.max_size / 128 / 1024.0 AS NUMERIC(18, 2)) AS max_size_GB,
    CAST(
        (CASE
        WHEN A.growth <= 0 THEN A.size / 128 / 1024.0
            WHEN A.max_size <= 0 THEN C.total_bytes / 1073741824.0
            WHEN A.max_size / 128 / 1024.0 > C.total_bytes / 1073741824.0 THEN C.total_bytes / 1073741824.0
            ELSE A.max_size / 128 / 1024.0
        END) AS NUMERIC(18, 2)) AS max_real_size_GB,
    CAST(NULL AS NUMERIC(18, 2)) AS free_space_GB,
    (CASE WHEN A.is_percent_growth = 1 THEN A.growth ELSE CAST(A.growth / 128 AS NUMERIC(18, 2)) END) AS growth_MB,
    A.is_percent_growth,
    (CASE WHEN A.growth <= 0 THEN 0 ELSE 1 END) AS is_autogrowth_enabled,
    CAST(NULL AS NUMERIC(18, 2)) AS percent_used,
    CAST(NULL AS INT) AS growth_times
INTO
    #Datafile_Size
FROM
    sys.master_files        A   WITH(NOLOCK)
    JOIN sys.databases      B   WITH(NOLOCK)    ON  A.database_id = B.database_id
    CROSS APPLY sys.dm_os_volume_stats(A.database_id, A.[file_id]) C
 
    
UPDATE A
SET
    A.free_space_GB = (
    (CASE
        WHEN max_size_GB <= 0 THEN A.disk_free_size_GB
        WHEN max_real_size_GB > disk_free_size_GB THEN A.disk_free_size_GB
        ELSE max_real_size_GB - size_GB
    END)),
    A.percent_used = (size_GB / (CASE WHEN max_real_size_GB > disk_total_size_GB THEN A.disk_total_size_GB ELSE max_real_size_GB END)) * 100
FROM
    #Datafile_Size A
    
 
UPDATE A
SET
    A.growth_times =
    (CASE
        WHEN A.growth_MB <= 0 THEN 0
        WHEN A.is_percent_growth = 0 THEN (A.max_real_size_GB - A.size_GB) / (A.growth_MB / 1024.0)
        ELSE NULL
    END)
FROM
    #Datafile_Size A
 
 
SELECT *
FROM #Datafile_Size

Script Result:

Columns returned:

ColumnDescription
database_idDatabase ID generated by SQL Server engine
database_nameDatabase Name
state_descDatabase status.

Possible values:
0 = ONLINE
1 = RESTORING
2 = RECOVERING | SQL Server 2008 to current version
3 = RECOVERY_PENDING | SQL Server 2008 to current version
4 = SUSPECT
5 = EMERGENCY | SQL Server 2008 to current version
6 = OFFLINE | SQL Server 2008 to current version
7 = COPYING | Azure SQL Database
10 = OFFLINE_SECONDARY | Azure SQL Database
type_descDatafile Type:
ROWS = Data Datafile
LOG = Datafile of transaction log data
file_idSQL Server engine generated datafile ID
nameDatafile Name
physical_namePhysical path of datafile on disk
disk_total_size_GBTotal size in GB of physical disk where datafile is
disk_free_size_GBFree space in GB of physical disk where datafile is
size_GBCurrent size (used) in GB of database
max_size_GBMaximum size in GB of datafile as configured in Autogrowth. If Autogrowth is off, it will show 0.00. If the Autogrowth limit is set to "Unlimited", this column will display negative values ​​or 2048 GB, depending on your SQL Server version.
max_real_size_GBREAL maximum size in GB of datafile. As explained above, some validations are made to get the actual datafile size, because in some situations, the maximum datafile size that the view returns is not the actual one.
free_space_GBFree size in GB of datafile
growth_MBSize in MB the datafile will grow due to Autogrowth events. If Autogrowth is off, it will show 0.00.
is_percent_growthBoolean column that tells you whether the datafile growth form is set to a size in MB (0 value) or a percentage value (1 value)
is_autogrowth_enabledBoolean column that tells you whether datafile Autogrowth is enabled (1 value) or disabled (0 value)
percent_usedReturns datafile space utilization percentage value
growth_timesReturns the number of times the datafile can grow through autogrowth events. This column is calculated only if the autogrowth type is set to grow by size. If autogrowth is set to grow by percentage, this column has a value of NULL.

How to monitor total, free and used space of databases

Guys, now that I have demonstrated how to check your datafiles disk space (there are several others on the internet), I am going to show you how to monitor your instance datafiles disk space and send alerts via email.

To assist in generating the HTML code, I will use stpExporta_Table_HTML_Output, but could also use fncExporta_Query_HTML, available at:

  • SQL Server - How to Email a Query Result in HTML Format Using CLR (C #)
  • How to export data from a SQL Server table to HTML

To send the email, I will use the Stored Procedure msdb.dbo.sp_send_dbmail, from SQL Server DatabaseMail. If you have not set up or did not know how to do it, learn more by visiting SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail).

Monitoring source code

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
IF (OBJECT_ID('dbo.stpMonitoramento_Tamanho_Datafiles') IS NULL) EXEC('CREATE PROCEDURE dbo.stpMonitoramento_Tamanho_Datafiles AS SELECT 1')
GO
 
ALTER PROCEDURE dbo.stpMonitoramento_Tamanho_Datafiles (
    @Vl_Limite FLOAT = 80
)
AS BEGIN
 
    ------------------------------------------------------------------------------------------------
    -- IDENTIFICAÇÃO DO ESPAÇO UTILIZADO PELOS DATAFILES
    ------------------------------------------------------------------------------------------------
 
    IF (OBJECT_ID('tempdb..#Monitor_Datafile_Size') IS NOT NULL) DROP TABLE #Monitor_Datafile_Size
    SELECT
        B.database_id AS database_id,
        B.[name] AS [database_name],
        A.state_desc,
        A.[type_desc],
        A.[file_id],
        A.[name],
        A.physical_name,
        CAST(C.total_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_total_size_GB,
        CAST(C.available_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_free_size_GB,
        CAST(A.size / 128 / 1024.0 AS NUMERIC(18, 2)) AS size_GB,
        CAST(A.max_size / 128 / 1024.0 AS NUMERIC(18, 2)) AS max_size_GB,
        CAST(
            (CASE
                WHEN A.growth <= 0 THEN A.size / 128 / 1024.0
                WHEN A.max_size <= 0 THEN C.total_bytes / 1073741824.0
                WHEN A.max_size / 128 / 1024.0 > C.total_bytes / 1073741824.0 THEN C.total_bytes / 1073741824.0
                ELSE A.max_size / 128 / 1024.0
            END) AS NUMERIC(18, 2)) AS max_real_size_GB,
        CAST(NULL AS NUMERIC(18, 2)) AS free_space_GB,
        (CASE WHEN A.is_percent_growth = 1 THEN A.growth ELSE CAST(A.growth / 128 AS NUMERIC(18, 2)) END) AS growth_MB,
        A.is_percent_growth,
        (CASE WHEN A.growth <= 0 THEN 0 ELSE 1 END) AS is_autogrowth_enabled,
        CAST(NULL AS NUMERIC(18, 2)) AS percent_used,
        CAST(NULL AS INT) AS growth_times
    INTO
        #Monitor_Datafile_Size
    FROM
        sys.master_files        A   WITH(NOLOCK)
        JOIN sys.databases      B   WITH(NOLOCK)    ON  A.database_id = B.database_id
        CROSS APPLY sys.dm_os_volume_stats(A.database_id, A.[file_id]) C
 
    
    UPDATE A
    SET
        A.free_space_GB = (
        (CASE
            WHEN max_size_GB <= 0 THEN A.disk_free_size_GB
            WHEN max_real_size_GB > disk_free_size_GB THEN A.disk_free_size_GB
            ELSE max_real_size_GB - size_GB
        END)),
        A.percent_used = (size_GB / (CASE WHEN max_real_size_GB > disk_total_size_GB THEN A.disk_total_size_GB ELSE max_real_size_GB END)) * 100
    FROM
        #Monitor_Datafile_Size A
    
 
    UPDATE A
    SET
        A.growth_times =
        (CASE
            WHEN A.growth_MB <= 0 THEN 0
            WHEN A.is_percent_growth = 0 THEN (A.max_real_size_GB - A.size_GB) / (A.growth_MB / 1024.0)
            ELSE NULL
        END)
    FROM
        #Monitor_Datafile_Size A
 
    
    IF (OBJECT_ID('tempdb..##Monitoramento_Datafile_Size') IS NOT NULL) DROP TABLE ##Monitoramento_Datafile_Size
    SELECT
        A.[database_name],
        A.[name],
        A.[type_desc],
        A.size_GB,
        A.max_real_size_GB ,
        A.free_space_GB,
        A.growth_MB,
        A.growth_times,
        A.percent_used
    INTO
        ##Monitoramento_Datafile_Size
    FROM
        #Monitor_Datafile_Size A
    WHERE
        percent_used >= @Vl_Limite
 
    
    ------------------------------------------------------------------------------------------------
    -- GERAÇÃO DOS ALERTAS
    ------------------------------------------------------------------------------------------------
    
    IF (OBJECT_ID('dbo.Historico_Tamanho_Datafile') IS NULL)
    BEGIN
        
        CREATE TABLE dbo.Historico_Tamanho_Datafile (
            Id_Evento INT IDENTITY(1, 1) NOT NULL,
            Dt_Evento DATETIME DEFAULT GETDATE() NOT NULL,
            Nm_Database VARCHAR(256) NOT NULL,
            Nm_Datafile VARCHAR(256) NOT NULL,
            Ds_Tipo VARCHAR(10) NOT NULL,
            Qt_Tamanho NUMERIC(18, 2) NOT NULL,
            Qt_Tamanho_Maximo NUMERIC(18, 2) NOT NULL,
            Qt_Espaco_Livre NUMERIC(18, 2) NOT NULL,
            Qt_Aumento_Autogrowth INT NOT NULL,
            Qt_Vezes_Autogrowth INT NULL,
            Pr_Utilizacao NUMERIC(5, 2)
        )
 
    END
 
    IF ((SELECT COUNT(*) FROM ##Monitoramento_Datafile_Size) > 0)
    BEGIN
        
 
        INSERT INTO dbo.Historico_Tamanho_Datafile (
            Nm_Database,
            Nm_Datafile,
            Ds_Tipo,
            Qt_Tamanho,
            Qt_Tamanho_Maximo,
            Qt_Espaco_Livre,
            Qt_Aumento_Autogrowth,
            Qt_Vezes_Autogrowth,
            Pr_Utilizacao
        )
        SELECT
            A.[database_name],
            A.[name],
            A.[type_desc],
            A.size_GB,
            A.max_real_size_GB ,
            A.free_space_GB,
            A.growth_MB,
            A.growth_times,
            A.percent_used
        FROM
            ##Monitoramento_Datafile_Size A
        
 
        DECLARE
            @Ds_Saida VARCHAR(MAX),
            @Assunto VARCHAR(200) = @@SERVERNAME + ' - Monitoramento de Espaço dos Datafiles',
            @Destinatario VARCHAR(MAX) = '[email protected]',
            @Mensagem VARCHAR(MAX)
 
            
        EXEC dbo.stpExporta_Tabela_HTML_Output
            @Ds_Tabela = '##Monitoramento_Datafile_Size', -- varchar(max)
            @Fl_Aplica_Estilo_Padrao = 1 , -- bit
            @Ds_Saida = @Ds_Saida OUTPUT -- varchar(max)
        
 
        SET @Mensagem = '
Prezado DBA,<br/>
Foi identificado um ou mais problemas de espaço em disco nos datafiles da instância ' + @@SERVICENAME + ' no servidor ' + @@SERVERNAME + ':<br/><br/>' + ISNULL(@Ds_Saida, '')
 
 
        -- Envia o e-mail
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'ProfileEnvioEmail' , -- sysname
            @recipients = @Destinatario , -- varchar(max)
            @subject = @Assunto, -- nvarchar(255)
            @body = @Mensagem, -- nvarchar(max)
            @body_format = 'HTML'
 
    END
 
 
END

Example of use:

Transact-SQL
1
2
EXEC dbo.stpMonitoramento_Tamanho_Datafiles
    @Vl_Limite = 40 -- float

Result:

I hope this post was helpful to you.
Hug!

tags: sqlsql server

You may also like ...

  • SQL Server - How to identify and replace “strange things” (hidden characters) in strings and tables

  • SQL Server - Using STRING_SPLIT to transform strings from a row into columns

  • SQL Server 2019 - News and Features List

  • Next Analysis Services - How to use XLMA to backup and restore cubes via the T-SQL command line
  • Previous SQL Server 2016 - How to Query Zip Code Information Using the Bemean API and JSON_VALUE Function

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.