#respirandoSQL - Script - Descobrindo o espaço em disco via SSMS

Olá,

Há um bom tempo não publico nenhum artigo — desde 2017 na verdade — mas como nunca é tarde para compartilhar conhecimento, e muito menos para aprender algo interessante, hoje lhes trago um script MUITO útil na administração de bancos de dados em geral.

Durante o dia-a-dia aqui na Empresa, frequentemente alguém me questiona sobre o espaço em disco nos servidores onde há SQL Server. É certo que há varias formas de obter essa informação (inclusive, monitoro essa métrica com afinco, mas isso é assunto para outro artigo), mas uma das mais fáceis, é através do próprio Management Studio, e é exatamente isso que o script abaixo possibilita:

 

EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
GO

EXEC sp_configure 'Ole Automation Procedures',1
RECONFIGURE
GO
SET NOCOUNT ON
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
DROP TABLE ##_DriveSpace
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
DROP TABLE ##_DriveInfo
DECLARE @Result INT
, @objFSO INT
, @Drv INT
, @cDrive VARCHAR(13)
, @Size VARCHAR(50)
, @Free VARCHAR(50)
, @Label varchar(50)
CREATE TABLE ##_DriveSpace
(
DriveLetter CHAR(1) not null
, FreeSpace VARCHAR(10) not null
)
CREATE TABLE ##_DriveInfo
(
DriveLetter CHAR(1)
, TotalSpace bigint
, FreeSpace bigint
, Label varchar(30)
)
INSERT INTO ##_DriveSpace
EXEC master.dbo.xp_fixeddrives
-- Iterate through drive letters.
DECLARE curDriveLetters CURSOR
FOR SELECT driveletter FROM ##_DriveSpace
DECLARE @DriveLetter char(1)
OPEN curDriveLetters
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @cDrive = 'GetDrive("' + @DriveLetter + '")'
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT
IF @Result = 0
EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT
IF @Result <> 0

EXEC sp_OADestroy @Drv
EXEC sp_OADestroy @objFSO
SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )
SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )
INSERT INTO ##_DriveInfo
VALUES (@DriveLetter, @Size, @Free, @Label)
END
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END
CLOSE curDriveLetters
DEALLOCATE curDriveLetters
PRINT 'Drive information for server ' + @@SERVERNAME + '.'
PRINT ''
-- Produce report.
SELECT DriveLetter
, Label
, FreeSpace/1024 AS [Free Space GB]
, (TotalSpace - FreeSpace)/1024 AS [Used Space GB]
, TotalSpace/1024 AS [Total Space GB]
, CONVERT(DECIMAL(9,0),(ROUND(((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100),0))) AS [Percentage Free]
FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC GO
DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo
GO
EXEC sp_configure 'Ole Automation Procedures',0
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
GO

O script, basicamente, habilita o uso de stored procedures de automações OLE no servidor, tais quais ficam desabilitados por padrão, captura as volumes dentro do servidor e calcula o tamanho dos mesmos. Seu uso ideal é através de um CMS, como pode ser observado no resultado abaixo:

Observação: a permissão mínima para execução desse script, é sysadmin, visto que usa procedimentos de automações OLE para capturar dados do sistema operacional.

E aí, o que achou? Gostou? Ficou com dúvidas? Deixe-me saber, envie uma mensagem aqui.

Até mais ;)