Views: 10.171 views
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
|
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:
| Column | Description |
|---|---|
| database_id | Database ID generated by SQL Server engine |
| database_name | Database Name |
| state_desc | Database 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_desc | Datafile Type: ROWS = Data Datafile LOG = Datafile of transaction log data |
| file_id | SQL Server engine generated datafile ID |
| name | Datafile Name |
| physical_name | Physical path of datafile on disk |
| disk_total_size_GB | Total size in GB of physical disk where datafile is |
| disk_free_size_GB | Free space in GB of physical disk where datafile is |
| size_GB | Current size (used) in GB of database |
| max_size_GB | Maximum 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_GB | REAL 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_GB | Free size in GB of datafile |
| growth_MB | Size in MB the datafile will grow due to Autogrowth events. If Autogrowth is off, it will show 0.00. |
| is_percent_growth | Boolean 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_enabled | Boolean column that tells you whether datafile Autogrowth is enabled (1 value) or disabled (0 value) |
| percent_used | Returns datafile space utilization percentage value |
| growth_times | Returns 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
|
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', @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:
|
1 2 |
EXEC dbo.stpMonitoramento_Tamanho_Datafiles @Vl_Limite = 40 -- float |
Result:
I hope this post was helpful to you.
Hug!
