Quando gerenciamos muitos computadores rodando instâncias de SQL Server sabemos o quanto difícil é verificar servidor por servidor para calcular o espaço em disco.

Geralmente contamos com uma solução simples:

Criamos um Central Management Server, registrando todos os bancos num local centralizado ou registramos os servidores localmente para facilmente rodarmos 1 script em diversas instâncias:

No exemplo acima conectamos em 14 servidores simultaneamente.

Para verificar o espaço em disco em um servidor a Microsoft disponibiliza a procedure XP_FIXEDDRIVES que nos traz uma listagem de Todas nossas UNIDADES de disco e seu espaço livre:

Isto é bastante útil para quando usamos servidores sem MOUNT POINT, onde cada disco recebe uma letra fixa de unidade (drive) e todo e qualquer dado gravado a partir da raiz desta unidade (ex: I:\) está gravando na mesma unidade lógica de disco.

Note que no resultado acima, a listagem traz 2 servidores que possuem somente 2 drives de storage e 2 drives internos, e um outro servidor com vários drives. Neste caso não temos um relatório preciso pois os dois primeiros servidores da listagem possuem MOUNT POINT.

Mount Point é como chamamos quando um volume de disco é assignado a um subdiretório/pasta de um drive existente. Logo o comando XP_FIXEDDRIVES não traz um resultado preciso, pois não nos mostra o espaço livre dos volumes em MOUNT POINT.

Como podemos ver no resultado do Disk Management, temos vários volumes anexados em nosso servidor para usarmos em nossos bancos de dados de SQL Server, porem Somente 4 possuem uma letra de unidade (drive), sendo C: e D: discos internos, Q: disco de quorum (neste caso este servidor é um nodo de cluster) e I: como unidade raiz do Storage reservado para armazenamento de banco de dados (Datafiles, log files, etc.). Podemos ver que todos os outros volumes disponíveis estão usando MOUNT POINT dentro do disco raiz I:\. Como gerenciamos este tipo de volume no SQL Server?

Até o momento não existe uma função conhecida no SQL Server que nos retorne os dados da mesma forma que a XP_FIXEDDRIVES nos volumes de MOUNT POINT.

Para contornarmos este problema vamos fazer uso do Windows PowerShell através do XP_CMDSHELL combinando o resultados da Classe WMI Win32_Volume mostrando exatamente quanto de espaco temos em cada servidor.

   1: Get-WmiObject -Class Win32_Volume -Filter 'DriveType = 3' | select name,capacity,freespace

Com o comando acima temos exatamente o que precisamos, então combinaremos ele com o XP_CMDSHEL e formataremos a saída de forma que teremos o mesmo resultado de XP_CMDSHEL porem com os MOUNT POINTS listados.

   1: DECLARE @svrName VARCHAR(255)

   2: DECLARE @sql VARCHAR(400)

   3:

   4: SET @sql = 'powershell.exe -c "Get-WmiObject -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

   5:

   6: CREATE TABLE #output (line VARCHAR(255))

   7:

   8: INSERT #output

   9: EXEC xp_cmdshell @sql

  10:

  11: SELECT rtrim(ltrim(SUBSTRING(line, 1, CHARINDEX('|', line) - 1))) AS drivename

  12:     ,round(cast(rtrim(ltrim(SUBSTRING(line, CHARINDEX('|', line) + 1, (CHARINDEX('%', line) - 1) - CHARINDEX('|', line)))) AS FLOAT), 0) AS 'capacity(MB)'

  13:     ,round(cast(rtrim(ltrim(SUBSTRING(line, CHARINDEX('%', line) + 1, (CHARINDEX('*', line) - 1) - CHARINDEX('%', line)))) AS FLOAT), 0) AS 'freespace(MB)'

  14: FROM #output

  15: WHERE line LIKE '[A-Z][:]%'

  16: ORDER BY drivename

  17: GO

  18:

  19: DROP TABLE #output

  20:

Executando o script acima agora teremos visível o espaço livre também nos MOUNT POINTS:

Agora temos uma forma rápida de gerenciar o espaço de disco em vários servidores mesmo que estejam utilizando MOUNT POINTS