Управление дисковым пространством является важной задачей для любого администратора SQL Server. Важно отслеживать использование диска, чтобы обеспечить оптимальную производительность и предотвратить возможные проблемы. В этой статье мы рассмотрим различные методы получения информации об использовании диска с использованием SQL Server и изучим использование T-SQL в сочетании с вызовом процедур автоматизации OLE и библиотек классов WMI.
Метод 1 – Использование автоматизации OLE и расширенных хранимых процедур
В этом методе мы включаем компоненты автоматизации OLE и конфигурации xp_cmdshell на SQL Server. Мы используем хранимую процедуру sp_OACreate для создания экземпляра класса Scripting.FileSystemObject OLE. Метод sp_OAMethod извлекает свойства каждого диска и файловой системы, и выполняется расширенная хранимая процедура xp_fixeddrives для получения информации о свободном месте на диске. Результат сохраняется во временной таблице и может использоваться для запросов и отчетов.
DECLARE @hr INT, @fso INT, @drive CHAR(1) = 'F', @odrive INT, @TotalSize BIGINT, @freespace BIGINT, @AvailableSpace BIGINT, @DriveCollection INT, @DriveCount INT, @FileSystemInstance INT, @volumeName varchar(128), @IsReady VARCHAR(5)
EXEC sp_OACreate 'Scripting.FileSystemObject', @fso OUT
EXEC sp_OAMethod @fso, 'GetDrive', @odrive OUT, @drive
EXEC sp_OAGetProperty @odrive, 'TotalSize', @TotalSize OUT
EXEC sp_OAGetProperty @odrive, 'freespace', @freespace OUT
EXEC sp_OAGetProperty @odrive, 'AvailableSpace', @AvailableSpace OUT
EXEC sp_OAGetProperty @fso, 'Drives', @DriveCollection OUT
EXEC sp_OAGetProperty @DriveCollection, 'Count', @DriveCount OUT
EXEC sp_OAGetProperty @odrive, 'VolumeName', @volumeName OUT
EXEC sp_OAGetProperty @odrive, 'isReady', @IsReady OUT
SELECT @DriveCount driveCount, @Drive DriveLetter, (@AvailableSpace/1048576)/1024 FreeSpace, @volumeName Volume, @IsReady DriveStatus, (@TotalSize/1048576)/1024.00 TotalSize, (@freespace/1048576)/1024.00 FreeSpaceМетод 2 – Запрос объекта WMI
В этом методе мы используем xp_cmdshell и PowerShell для запроса класса WMI Win32_Volume и сбора имен томов, свободного пространства и общего размера во временную таблицу. Этот метод позволяет отслеживать использование дискового пространства на серверах, не являющихся SQL-серверами. Результат можно использовать для планирования емкости и создания отчетов.
DECLARE @svrName VARCHAR(255)
DECLARE @sql varchar(400)
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+''*''}"'
CREATE TABLE #output (line varchar(255))
INSERT #output EXEC xp_cmdshell @sql
CREATE TABLE #drives (id INT IDENTITY(1,1) PRIMARY KEY, drive CHAR(5), FreeSpaceGB INT, TotalSizeGB INT NULL, percentageOfFreeSpace decimal(5,2))
INSERT INTO #drives(drive,TotalSizeGB,FreeSpaceGB,percentageOfFreeSpace)
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)',
cast(round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,(CHARINDEX('*',line)-1)-CHARINDEX('%',line)))) as Float)/1024,0)/round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,(CHARINDEX('%',line)-1)-CHARINDEX('|',line)))) as Float)/1024,0)*100 as decimal(5,2)) as '%Free'
FROM #output
WHERE line like '[A-Z][:]%'
ORDER BY drivenameЗаключение
Отслеживание использования диска в SQL Server является важным для поддержания оптимальной производительности и предотвращения возможных проблем. С помощью методов, таких как автоматизация OLE и запрос объектов WMI, администраторы могут получать информацию о дисковом пространстве и использовать ее для планирования емкости и создания отчетов. Важно обеспечить наличие соответствующих мер безопасности при включении автоматизации OLE и xp_cmdshell.