Рано или поздно, но у многих возникает вопрос, как определить список дисков, их общий объём и объём свободного пространства на них, при этом используя t-sql. На первый взгляд кажется, что задача должна решаться на раз, два, три. Но не все так просто.
Для решения этой задачи можно использовать несколько сценариев. Во первых хотелось бы вспомнить о недокументированной хранимой процедуре xp_fixedrives. Это очень полезная расширенная хранимая процедура, которая возвращает список всех установленных жестких дисков и размер в МБ свободного пространства для каждого жесткого диска.
exec master..xp_fixeddrives
Результат её работы:
drive MB free
----- -----------
C 8147
F 42048
К сожалению она выводит только объём свободного пространства. Для получения общего объёма дисков подобной хранимой процедуры, к сожалению, нет. Но не стоит отчаиваться.
Для этого можно написать свою процедуру с использованием объектов OLE-автоматизации. (http://msdn.microsoft.com/ru-ru/library/ms191188.aspx). Начиная с SQL Server 2005 по умолчанию они отключены и их необходимо включить с помощью sp_configure. Пример самой процедуры:
create proc sp_DriveSpace
@DrivePath varchar(1024)
, @TotalSpace float output
, @FreeSpace float output
as
DECLARE @fso int
, @Drive int
, @DriveName varchar(255)
, @Folder int
, @Drives int
, @source varchar(255)
, @desc varchar(255)
, @ret int
, @Object int
-- Создаем обект файловой системы
exec @ret = sp_OACreate 'Scripting.FileSystemObject', @fso output
set @Object = @fso
if @ret != 0
goto ErrorInfo
-- Получаем папку по заданному пути
exec @ret = sp_OAmethod @fso, 'GetFolder', @Folder output, @DrivePath
set @Object = @fso
if @ret != 0
goto ErrorInfo
-- Получаем устройство
exec @ret = sp_OAmethod @Folder, 'Drive', @Drive output
set @Object = @Folder
if @ret != 0
goto ErrorInfo
-- Определяем полный размер устройства
exec @ret = sp_OAGetProperty @Drive, 'TotalSize', @TotalSpace output
set @Object = @Drive
if @ret != 0
goto ErrorInfo
-- Определяем свободное место не устройстве
exec @ret = sp_OAGetProperty @Drive, 'AvailableSpace', @FreeSpace output
set @Object = @Drive
if @ret != 0
goto ErrorInfo
DestroyObjects:
if @Folder is not null
exec sp_OADestroy @Folder
if @Drive is not null
exec sp_OADestroy @Drive
if @fso is not null
exec sp_OADestroy @fso
return (@ret)
ErrorInfo:
exec sp_OAGetErrorInfo @Object, @source output, @desc output
print 'Source error: ' + isnull( @source, 'n/a' ) + char(13) + 'Description: ' + isnull( @desc, 'n/a' )
goto DestroyObjects
Вызов:
declare @TotalSpace float
, @FreeSpace float
exec sp_DriveSpace @DrivePath = 'C:'
, @TotalSpace = @TotalSpace out
, @FreeSpace = @FreeSpace out
select @TotalSpace/1024/1024 as [TotalSpace (Mb)]
, @FreeSpace/1024/1024 as [FreeSpace (Mb)]
И результат её работы:
TotalSpace (Mb) FreeSpace (Mb)
---------------------- ----------------------
38051,99609375 8143,734375
Кроме того размер дисков можно получить с помощью процедуры xp_cmdshell. Она так же, по умолчанию, отключена. Включить ее можно с помощью хранимой процедуры sp_configure или управления на основе политик. (http://msdn.microsoft.com/ru-ru/library/ms190693.aspx). Через xp_cmdshell мы можем выполнить команду Fsutil volume.
Команда fsutil volume позволяет размонтировать том или проверить объем свободного дискового пространства на томе. Команда имеет следующий синтаксис:
fsutil volume <diskfree | dismount > <drivename | volumepath>
Синтаксис команды требует указания параметра diskfree или dismount. Кроме того необходимо указать букву диска или путь к тому. Параметры этой команды более подробно рассматриваются в следующей таблице.
Параметры команды fsutil volume
Параметр
|
Использование
|
diskfree
|
Используется для запроса объема свободного дискового пространства на указанном диске или томе
|
dismount
|
Используется для размонтирования указанного диска или тома
|
drivename
|
Используется для указания логического диска, над которым выполняется команда
|
volumepath
|
Используется для указания логического пути к точке монтирования или имени тома, который представляет логический том
|
Параметр diskfree идеально подходит для проверки дискового пространства тома, когда есть подозрения в его недостатке. Параметр dismount предоставляет способ быстро завершить любой открытый процесс или пользовательский сеанс, который в данный момент получает доступ к тому.
Размонтирование тома может оказаться необходимым при запуске служебного приложения над этим томом, когда программа не может продолжать работу из-за другого приложения, осуществляющего доступ к тому. Размонтирование тома должно решить подобную проблему и программа должно нормально выполнить свою задачу при следующем запуске.
Пример вызова:
exec master..xp_cmdshell 'fsutil volume diskfree C:'
И результат её работы:
output
---------------------------------------------
Всего свободно байт : 8533823488
Всего байт : 39900409856
Всего доступно свободных байт : 8533823488
Ещё один вариант - это использование Windows Management Instrumentation (WMI) (a href="http://ru.wikipedia.org/wiki/WMI">http://ru.wikipedia.org/wiki/WMI) через xp_cmdshell. Если быть точнее, то мы будем вызывать утилиту WMIC Программа WMIC представляет собой командную строку и интерфейс написания сценариев, которые упрощают использование инструментария управления Windows (WMI) и систем, управляемых с помощью WMI. Программа WMIC основана на псевдонимах. С помощью псевдонимов можно получать доступ к основным данным инструментария WMI, не имея представления о его устройстве. Данные WMI и возможности WMI доступны через инструментарий управления без псевдонимов. Пример:
exec master..xp_cmdshell 'wmic logicaldisk where deviceid="c:" get Size, FreeSpace'
Результат:
output
------------------------
FreeSpace Size
8533590016 39900409856
Если на сервере с БД установлен PowerShell, то есть ещё одно решение:
set nocount on
declare @Cmd varchar(4000)
set @Cmd = ' ""C:\WINDOWS\system32\windowspowershell\v1.0\powershell" "& Get-PSDrive -psprovider filesystem " '
exec master..xp_cmdshell @Cmd
Результат:
Name Used (GB) Free (GB) Provider Root
---- --------- --------- -------- ----
C 29,21 7,95 FileSystem C:\
D FileSystem D:\
F 191,82 41,06 FileSystem F:\
И это ещё не всё, если версия вашего SQL Server`a 2005 и выше, то можно использовать CLR-сборку. Код сборки на C#:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void isp_DiskSpace(string serverName)
{
if (serverName == "")
{
serverName = Environment.MachineName;
}
PerformanceCounterCategory pcc = new PerformanceCounterCategory("LogicalDisk", serverName);
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Drive/MountPoint", SqlDbType.NVarChar, 256),
new SqlMetaData("Capacity (MB)", SqlDbType.VarChar, 256),
new SqlMetaData("Used Space (MB)", SqlDbType.VarChar, 256),
new SqlMetaData("Free Space (MB)", SqlDbType.VarChar, 256),
new SqlMetaData("Percent Free Space", SqlDbType.VarChar, 6));
SqlContext.Pipe.SendResultsStart(record);
foreach (string instanceName in pcc.GetInstanceNames())
{
PerformanceCounter pcPercentFree = new PerformanceCounter("LogicalDisk", "% Free Space", instanceName, serverName);
PerformanceCounter pcFreeMbytes = new PerformanceCounter("LogicalDisk", "Free Megabytes", instanceName, serverName);
float percentfree = pcPercentFree.NextValue();
float freespace = pcFreeMbytes.NextValue();
float capacity = (freespace * 100) / percentfree;
float usedspace = capacity - freespace;
if (instanceName != "_Total")
{
record.SetSqlString(0, instanceName);
record.SetSqlString(1, capacity.ToString());
record.SetSqlString(2, usedspace.ToString());
record.SetSqlString(3, freespace.ToString());
record.SetSqlString(4, percentfree.ToString());
SqlContext.Pipe.SendResultsRow(record);
}
}
SqlContext.Pipe.SendResultsEnd();
}
};
После компиляции "подключаем" нашу dll-ку:
USE master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
ALTER DATABASE MyDB SET TRUSTWORTHY ON
GO
USE MyDB
GO
CREATE ASSEMBLY DiskSpace
FROM 'C:\SQLTools\DiskSpace.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE PROC dbo.isp_DiskSpace @serverName nvarchar(4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME DiskSpace.StoredProcedures.isp_DiskSpace
GO
Ну и сам пример её работы (вызова):
--local server
EXEC dbo.isp_DiskSpace @serverName = ''
EXEC dbo.isp_DiskSpace @serverName = 'LocalServer'
--remote server
EXEC dbo.isp_DiskSpace @serverName = 'RemoteServer'
Скомпилированную сборку прилагаю:
DiskSpace.dll (16,00 kb).
Если вам знакомы ещё какие-то решения данной задачи, то пишите, а я с удовольствием пополню эту статью вашими примерами.