use master

DROP PROCEDURE IF EXISTS spacehd;

GO

CREATE PROCEDURE spacehd AS

/*crio tabela espaco em disco se não existir*/

if object_id('espacodisco') is null

begin

CREATE TABLE espacodisco (

Montagem varchar (10) ,

Volume varchar (10) ,

[Total_GB] Int,

[Disponivel_GB] Int,

[Disponivel_P] Int,

[Uso_P] Int,

[Data] smalldatetime

  )

end 

/*obtenho os dados de espaco em disco */

insert into espacodisco

SELECT DISTINCT

VS.volume_mount_point [Montagem] ,

VS.logical_volume_name AS [Volume] ,

CAST(CAST(VS.total_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Total (GB)] ,

CAST(CAST(VS.available_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Espaço Disponível (GB)] ,

CAST(( CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço Disponível ( % )] ,

CAST(( 100 - CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço em uso ( % )]

,GETDATE()

FROM

sys.master_files AS MF

CROSS APPLY [sys].[dm_os_volume_stats](MF.database_id, MF.file_id) AS VS

WHERE

CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 < 100

/* select * from espacodisco */

return

 

/* Para executar a obtenção dos dados bastar colocar a linha abaixo dentro do codigo 

EXEC master.dbo.spacehd

*/