Информация о дисках в SQL Server

По мотивам статьи: https://www.mssqltips.com/sqlservertip/2444/script-to-get-available-and-free-disk-space-for-sql-server/

Проблема

Часто мы сталкиваемся с ситуацией, когда нужно узнать общий размер и свободное место на всех дисках в системе (в т.ч. LUN/Mount устройства). Расширенная хранимая процедура XP_FixedDrives не может помочь в таком случае (вернёт только свободное место на фиксированных локальных дисках), и обычно для решения нам приходится заходить на сервер (по RDP) и проверять интерактивно общий размер и свободное место на дисках. В этой статье будет показано как решить такую задачу прямо в SQL Server, используя Powershell командлеты. В самом конце будет представлена моя процедура usp_GetDrives2, которая возвращает полную информацию о дисках на сервере.

Решение

Прежде чем продолжить, давайте обсудим - что такое LUN/Mount устройства. LUN/Mount устройства - это логический блок СХД, созданный и настроенный администратором СХД и подключенный в операционную систему сервера. Сам сервер не знает физического строения дисков, участвующих в этом устройстве и видит его как простой логический подключенный диск. Вот почему хранимка XP_FixedDrives не возвращает никаких данных по  LUN/Mount устройствам.

Мы можем проверить место на дисках с помощью Powershell-скрипта, который мы запустим через расширенную хранимую процедуру XP_CMDSHELL. Чтобы это сделать, нужно разрешить использование этой процедуры в настройках экземпляра SQL Server.

Вы можете выполнить следующий T-SQL скрипт для проверки и включения процедуры XP_CMDSHELL. Для запуска должны быть разрешения ALTER SETTINGS на уровне сервера. Такие разрешения есть у встроенных серверных ролей sysadmin и serveradmin .

declare @chkCMDShell as sql_variant;
select @chkCMDShell = value from sys.configurations where name = 'xp_cmdshell';
if @chkCMDShell = 0
begin
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
end
else
begin
PRINT 'xp_cmdshell is already enabled';
end

Теперь рассмотрим T-SQL скрипт для получения информации по всем дискам (в т.ч. LUN/Mount устройствам).
Этот скрипт использует командлеты Powershell через вызов хранимой процедуры XP_CMDSHELL, таким образом можно выполнить этот скрипт прямо в окне запросов SSMS (Management Studio) и получить все результаты. Конечно, можно выполнить отдельно Powershell скрипт и получить эти же результаты, но мне очень хочется получать эти результаты прямо в запросе T-SQL.
Вот этот скрипт:

declare @svrName varchar(255)
declare @sql varchar(400)
--by default it will take the current server name, we can the set the server name as well
set @svrName = @@SERVERNAME
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
--creating a temporary table
CREATE TABLE #output(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql
--script to retrieve the values in MB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'
from #output
where line like '[A-Z][:]%'
order by drivename
--script to retrieve the values in GB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #output
where line like '[A-Z][:]%'
order by drivename
--script to drop the temporary table
drop table #output

Я выполню этот скрипт и выполню хранимую процедуру XP_FixedDrives на одном и том же сервере. И мы увидим, что XP_FixedDrives не показывает общий размер дисков, а также информацию по LUN/Mount устройствам. 

Результат выполнения ниже на картинке. Здесь хорошо видно, что XP_FixedDrives возвращает информацию о физических дисках L: и F:, но при этом эта процедура не может получить информацию по LUN или Mount устройствам (например, L:\UserDBData1 и F:\UserDBLog1 ):

usp_GetDrives2

На основании всего вышесказанного я создал свою хранимую процедуру для получения полной информации по дискам на SQL Server.
Код процедуры:
use sputnik;
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists(select object_id from sys.objects where name='usp_GetDrives2')
DROP PROCEDURE info.usp_GetDrives2;
go
/* =============================================
-- Author: Андрей Иванов (sqland1c)
-- Create date: 03.08.2016 (1.0)
-- Description: Новая процедура, возвращает информацию о локальных дисках и свободном месте в Гб на Них в виде Таблицы!
В отличии от первой версии (info.usp_GetDrives) эта процедура возвращает наиболее
полную информацию (метка диска, весь объем диска).
-- Update:
-- ============================================= */
CREATE PROCEDURE info.usp_GetDrives2 
@Details bit=1
AS
BEGIN
set nocount ON;
declare @sql varchar(400)
--declare @svrName varchar(255)
--По умолчанию выводим информацию для текущего Компьютера. НО также можно указать другой компьютер в параметре -ComputerName
--set @svrName = CAST(SERVERPROPERTY('MachineName') as varchar(255));
--set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace,label | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''+$_.label+''@''}"'

/*--ТипДиска:
Value Meaning
0 (0x0) Unknown
1 (0x1) No Root Directory
2 (0x2) Removable Disk
3 (0x3) Local Disk
4 (0x4) Network Drive
5 (0x5) Compact Disk
6 (0x6) RAM Disk
*/
set @sql = 'powershell.exe -c "Get-WmiObject -Class Win32_Volume | select name,capacity,freespace,label,FileSystem,DriveType | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''+$_.label+''@''+$_.FileSystem+''#''+$_.DriveType+''!''}"';
if object_id('tempdb.dbo.#output') is not null
drop table #output;
CREATE TABLE #output
(line varchar(255))
insert #output
EXEC xp_cmdshell @sql
--select * from #output
----script to retrieve the values in MB from PS Script output
--select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
--      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
--      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'
--      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
--      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'
--from #output
--where line like '[A-Z][:]%'
--order by drivename
--script to retrieve the values in GB from PS Script output
IF @Details=1 
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as Drive
 ,
 CASE rtrim(ltrim(SUBSTRING(line,CHARINDEX('#',line)+1,(CHARINDEX('!',line) -1)-CHARINDEX('#',line))))
WHEN 0 THEN 'Unknown' 
WHEN 1 THEN 'No_Root_Directory' 
WHEN 2 THEN 'Removable' 
WHEN 3 THEN 'Fixed'
WHEN 4 THEN 'Network'
WHEN 5 THEN 'CD-ROM'
WHEN 6 THEN 'RAM_Disk'
 END as DriveType
 ,rtrim(ltrim(SUBSTRING(line,CHARINDEX('*',line)+1,(CHARINDEX('@',line) -1)-CHARINDEX('*',line)))) as Label
 ,rtrim(ltrim(SUBSTRING(line,CHARINDEX('@',line)+1,(CHARINDEX('#',line) -1)-CHARINDEX('@',line)))) as FileSystem
 ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
 (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'GB_Capacity'
 ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
 (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'GB_Free'
from #output
where line like '[A-Z][:]%'
order by Drive
ELSE
select LEFT(rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))),1) as Drive
 ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
 (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'GB_Capacity'
 ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
 (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'GB_Free'
from #output
where line like '[A-Z][:]%'
AND rtrim(ltrim(SUBSTRING(line,CHARINDEX('#',line)+1,(CHARINDEX('!',line) -1)-CHARINDEX('#',line))))=3
order by Drive
if object_id('tempdb.dbo.#output') is not null
drop table #output;
END
GO


Пример вызова процедуры и результаты: