Объём дисков на T-SQL

by Alexey Knyazev 20. февраля 2011 21:49

Рано или поздно, но у многих возникает вопрос, как определить список дисков, их общий объём и объём свободного пространства на них, при этом используя 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).


Если вам знакомы ещё какие-то решения данной задачи, то пишите, а я с удовольствием пополню эту статью вашими примерами.

Tags: , ,

PowerShell | SQL Server

Комментарии (5) -

Agapov.A
Agapov.A Russia
22.02.2011 15:08:11 #

Подозреваю можно использовать системные счетчики:
sys.dm_os_performance_counters

Reply

Alexey Knyazev
Alexey Knyazev Russia
22.02.2011 21:03:02 #

К сожалению там только счетчики сиквела

Reply

Alexey Knyazev
Alexey Knyazev Russia
25.11.2011 13:48:05 #

В SQL Server 2008R2 - sys.dm_os_volume_stats (database_id, file_id)

Reply

Lepsik
Lepsik Canada
29.10.2013 6:03:53 #

----Если вам знакомы ещё какие-то решения данной задачи, то пишите, а я с удовольствием пополню эту статью вашими примерами.

легко : www.codeproject.com/.../Direct-access-to-Cplusplus-native-code-from-Cplusp

Reply

Lepsik
Lepsik Canada
19.11.2013 6:12:54 #

еше можно читать прямо с контороллера дисков

www.codeproject.com/.../ViewDownloads.aspx


как приложить проект не нашел

Reply

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading