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

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

В качестве ещё одного небольшого приложения на PowerShell я написал небольшой скрипт, который позволит работать с вашими базами данных. Это небольшое самостоятельное Windows-приложение с визуальными формами, которое позволяет писать запросы, а результат запроса можно не долько просматривать в виде удобного GridView, но и применять различные фильтры

Ниже скрипт этой небольшой консоли:

#Блок Try/Catch/Finally, для обработки ошибок
function Try
{
    param
    (
        [ScriptBlock]$Command = $(throw "The parameter -Command is required."),
        [ScriptBlock]$Catch   = { throw $_ },
        [ScriptBlock]$Finally = {}
    )
    
    & {
        $local:ErrorActionPreference = "SilentlyContinue"
        
        trap
        {
            trap
            {
                & {
                    trap { throw $_ }
                    &$Finally
                }
                
                throw $_
            }
            
            $_ | & { &$Catch }
        }
        
        &$Command
    }

    & {
        trap { throw $_ }
        &$Finally
    }
}


# Создание формы 
[void][reflection.assembly]::LoadWithPartialName("System.Windows.Forms")
[void][reflection.assembly]::LoadWithPartialName("System.Data")
[void][reflection.assembly]::LoadWithPartialName("System.Data.Sql")
[void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")

$form = new-object Windows.Forms.Form
$form.Text = "SQL Console (Power Shell)"
$form.WindowState="Maximized"
$form.startposition = "CenterScreen"
$form.autosize = 0 

# add a labelServer
$labelServer = new-object Windows.Forms.label
$labelServer.Location = New-Object System.Drawing.Size(5,10)
$labelServer.autosize = 1
$labelServer.Text="Server Name:"
# add a TextBoxServer
$TextBoxServer = new-object Windows.Forms.TextBox
$TextBoxServer.Location = New-Object System.Drawing.Size(100,10)
$TextBoxServer.autosize = 1
$TextBoxServer.Width=180
$TextBoxServer.Text="."

# add a labelAuth
$labelAuth = new-object Windows.Forms.label
$labelAuth.Location = New-Object System.Drawing.Size(5,35)
$labelAuth.autosize = 1
$labelAuth.Text="Authentication:"
# add a TextBoxAuth
$TextBoxAuth = new-object Windows.Forms.ComboBox
$TextBoxAuth.Location = New-Object System.Drawing.Size(100,35)
$TextBoxAuth.autosize = 1
$TextBoxAuth.Width=180
$TextBoxAuth.Items.Add("Windows Authentication")
$TextBoxAuth.Items.Add("SQL Server Authentication")
$TextBoxAuth.SelectedIndex=0;

# add a labelLogin
$labelLogin = new-object Windows.Forms.label
$labelLogin.Location = New-Object System.Drawing.Size(5,60)
$labelLogin.autosize = 1
$labelLogin.Text="Login:"

# add a TextBoxLogin
$TextBoxLogin = new-object Windows.Forms.TextBox
$TextBoxLogin.Location = New-Object System.Drawing.Size(100,60)
#$TextBoxLogin.autosize = 1
$TextBoxLogin.Width=180

# add a labelPassword
$labelPassword = new-object Windows.Forms.label
$labelPassword.Location = New-Object System.Drawing.Size(5,85)
$labelPassword.autosize = 1
$labelPassword.Text="Password:"

# add a TextBoxLogin
$TextBoxPassword = new-object Windows.Forms.TextBox
$TextBoxPassword.Location = New-Object System.Drawing.Size(100,85)
$TextBoxPassword.autosize = 1
$TextBoxPassword.Width=180
$TextBoxPassword.UseSystemPasswordChar = "true"

# add a PanelConnect
$PanelConnect = new-object Windows.Forms.Panel
$PanelConnect.Location = New-Object System.Drawing.Size(0,0)
$PanelConnect.autosize = 1
$PanelConnect.Height=270
$PanelConnect.Dock="Top"

# add a PanelQuery
$PanelQuery = new-object Windows.Forms.Panel
$PanelQuery.Location = New-Object System.Drawing.Size(10,180)
$PanelQuery.autosize = 1
$PanelQuery.Dock="Fill"

# add a PanelButton
$PanelButton = new-object Windows.Forms.Panel
$PanelButton.Location = New-Object System.Drawing.Size(10,200)
$PanelButton.autosize = 1
$PanelButton.Dock="Bottom"

# add a Scroll1
$Scroll1 = new-object Windows.Forms.Splitter
$Scroll1.Location = New-Object System.Drawing.Size(10,191)
$Scroll1.Dock="Bottom"

# add a TextBoxResult
$TextBoxResult = new-object Windows.Forms.TextBox
$TextBoxResult.Location = New-Object System.Drawing.Size(0,0)
$TextBoxResult.autosize = 1
$TextBoxResult.Multiline="true"
$TextBoxResult.Dock="Fill"
$TextBoxResult.ScrollBars="Vertical"

# add a button 
$button = new-object Windows.Forms.button
$button.Location = New-Object System.Drawing.Size(5,5)
$button.text = "Execute SQL"
$button.width = 100

$button.add_click(
{
#Вот тут кусок для работы с БД
$Query = $TextBoxResult.text
$Connect = $TextBoxServer.text
$Login = $TextBoxLogin.text
$Password = $TextBoxPassword.text
$Aut = $TextBoxAuth.text

if ($Aut -eq 'SQL Server Authentication')
{
$SQLConnection = new-object System.Data.SqlClient.SqlConnection("Data Source='$Connect';User='$Login';password='$Password';")
}
else
{
$SQLConnection = new-object System.Data.SqlClient.SqlConnection("Data Source='$Connect';Integrated Security=SSPI")
}

$SQLConnection.Open()
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($Query, $SQLConnection)

$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLCommand)

$DataSet = New-Object System.Data.DataSet;
$SQLAdapter.Fill($DataSet);
$DataTable=New-Object System.Data.DataTable
$DataTable=$DataSet.Tables[0]


Try {
    $SQLCommand.ExecuteReader()
} Catch {
    $SQLCommand.EndExecuteNonQuery()
} Finally {
    { Write-Warning "Ошибка!" }; 
}

$Grid.DataSource=$DataTable.DefaultView | Out-GridView;
$SQLConnection.Close()
}
) 

# Вывод формы на экран
$PanelConnect.controls.add($labelServer)
$PanelConnect.controls.add($labelAuth)
$PanelConnect.controls.add($labelLogin)
$PanelConnect.controls.add($labelPassword)

$PanelConnect.controls.add($TextBoxServer)
$PanelConnect.controls.add($TextBoxAuth)
$PanelConnect.controls.add($TextBoxLogin)
$PanelConnect.controls.add($TextBoxPassword)

$form.controls.add($Scroll2)
$PanelQuery.controls.add($TextBoxResult)
$form.controls.add($PanelQuery)

$form.controls.add($PanelConnect)

$PanelButton.controls.add($button)
$form.controls.add($PanelButton)

$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

Скрипт для загрузки: sqlposh.ps1 (5,71 kb)

Мой доклад на TechDays.RU 

PowerShell стал неотъемлемой частью большинства новых продуктов Microsoft, в том числе и SQL Server. Данный доклад показывает всю мощь командной строки для администрирования ваших серверов БД.

Давно возникла такая идея и это произошло!!!

За хостинг большое спасибо .masterhost

На форуме "TechNet RUS SQL Forum" появился вопрос:

Можно ли сразу из БД при помощи MS SQL перевести значения полей в AD в виде пользователей. (К примеру по фамилии имеющихся в БД людей создать пользователей в AD с логином <фамилия>)? (http://social.technet.microsoft.com/Forums/ru-RU/sqlru/thread/54ca41d0-d70c-4318-8b5c-daa7aecce9f5)

В качестве решения подобной задачи предлагаю небольшой скрипт на PowerShell.

Для начала создадим тестовую таблицу в БД с Логином и Паролей пользователей (для демонстрации всего 5 юзеров):

CREATE TABLE Users (UserLogin sysname, UserPassword nvarchar(255))
GO

INSERT INTO Users
SELECT 'SQLUser1', '1234567890'
UNION ALL
SELECT 'SQLUser2', '1234567890'
UNION ALL
SELECT 'SQLUser3', '1234567890'
UNION ALL
SELECT 'SQLUser4', '1234567890'
UNION ALL
SELECT 'SQLUser5', '1234567890

Для работы с AD буду использовать бесплатный набор командлетов (часто называемых также AD cmdlets или QAD cmdlets), доступный с сайта http://www.quest.com/activeroles_server/arms.aspx.

А для создания пользователя команду New-QADUser, полный синтаксис команды доступен в wiki: http://wiki.powergui.org/index.php/New-QADUser

$Domen = "MyServerAD" #Read-Host "Введите адресс контролера"
$User = "MyUser" #read-host "Введите логин"
$Password =read-host "Введите пароль" -AsSecureString;

#Подключаемся к AD
Connect-QADService -Service $Domen -ConnectionAccount $User  -ConnectionPassword $Password;

#Создаём подключение к серверу БД
$SQLConnection = new-object System.Data.SqlClient.SqlConnection("Data Source='MyServerBD';User='MyUser';password='MyPassword';")
#Запрос к таблице с Юзерами
$Query="select * from Test.dbo.Users"
$SQLConnection.Open()
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($Query, $SQLConnection)

$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLCommand)

$DataSet = New-Object System.Data.DataSet;
$SQLAdapter.Fill($DataSet);
$DataTable=New-Object System.Data.DataTable
$DataTable=$DataSet.Tables[0]
$rdr=$SQLCommand.ExecuteReader()

#В цикле по всему рекордсету перебераю записи с юзерами
While($rdr.Read())
{
#Выводим логин
Write-Host $rdr[0]
#Создаём пользователя в AD
New-QADUser -Name $rdr[0] -ParentContainer "MyServerAD.com/Users"  -SamAccountName $rdr[0] -UserPassword $rdr[1]
}
#Закрываем подключение к Серверу БД
$SQLConnection.Close();
#Отключаемся от AD
Disconnect-QADService;

Скрипт достаточно примитивный, но работает и если его довести до ума, то можно в AD экспортировать и другую информацию о пользователе.

Ранее я писал, как с помощью PowerShell создать группы пользователей AD: http://www.t-sql.ru/post/ADGroupsPoSh.aspx

В одной из своих заметок в блоге я публиковал, как можно с помощью PowerShell заскриптовать все объекты той или иной БД на сервере баз данных (Скриптуем объекты БД (PoSh) ), а на днях в коментариях в этому скрипту появился вопрос, как заскриптовать сами данные, а не только "скелет" базы.

Но прежде чем показать скрипт на PowerShell, перечислю другие возможные способы реализации данной задачи

Во первых - подумайте, действительно ли вам необходимы данные в виде файла, не проще ли (а самое главное правильнее) сделать Бэкап базы

Второй вариант - это T-SQL скрипт, т.к. в сети легко найти подобное решение, то я не стал изобретать велосипед, а предлагаю воспользоваться скриптом Narayana Vyas Kondreddi: SQL Server 2000 либо SQL Server 2005/2008

Ещё одно решение - стандартный клиент для работы с SQL Server 2005 и выше: SSMS (Microsoft SQL Server Management Studio). Для SSMS2005 - это надстройка (ADD-IN) Generate Insert statements from resultsets, tables or database из пакета дополнительных надстроек http://www.ssmstoolspack.com/

При этом в SSMS2008 подобная надстройка добавлена: Tasks->Generate Scripts..., а дальше в Wizard`e отметить, что в скрипт мы хотим включить не только объекты БД, но и данные.

Ну и вариант на PowerShell с использованием SMO:

$server=Read-Host "Укажите имя сервера БД"
$database=Read-Host "Имя БД"
$output_file=Read-Host "Вывести результат в файл (укажите имя файла)"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table")
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)

$db = $srv.Databases[$database]

$scripter.Options.ScriptSchema = $true;
$scripter.Options.ScriptData = $true;
$scripter.Options.DriAll = $true
$scripter.Options.DriAllConstraints = $true
$scripter.Options.DriAllKeys = $true
$scripter.Options.DriChecks = $true
$scripter.Options.DriClustered = $true
$scripter.Options.DriDefaults = $true
$scripter.Options.DriForeignKeys = $true
$scripter.Options.DriIncludeSystemNames = $true
$scripter.Options.DriIndexes = $true
$scripter.Options.DriNonClustered = $true
$scripter.Options.DriPrimaryKey = $true
$scripter.Options.DriUniqueKeys = $true

$scripter.Options.AppendToFile =$true

$scripter.Options.FileName = $output_file
$scripter.Options.ToFileOnly = $true

foreach ($tbl in $db.Tables)   
{   
foreach ($s in $scripter.EnumScript($tbl)) { write-host $s }   
} 

Список пар-ов ScriptingOptions, можно посмотреть на MSDN http://msdn.microsoft.com/ru-ru/library/microsoft.sqlserver.management.smo.scriptingoptions_members.aspx

С новым, 2010 годом, коллеги!!! Здоровья, счастья, любви и конечно же новых побед! С праздником!

Продолжая тему скриптов на PowerShell в помощь администраторам БД, предлагаю вариант Job Activity Monitor(Журнал выполнения заданий) на PowerShell.

Так как задания SQL Server Agent чаще всего выполняются по расписанию, то, скорее всего, вам потребуется просматривать историю их выполнения, например для того, чтобы убедиться, что они выполняются успешно и каких-либо проблем не возникает.

Год назад я публиковал вариант в виде Windows-приложения (http://www.itcommunity.ru/blogs/mssql/archive/2008/12/04/40235.aspx), теперь более лёгкий и гибкий скрипт.

#Строка коннекции к БД (в данном случае с Windows-авторизацией)
$ConnectionString="Data Source=(local);Trusted_Connection=yes;Integrated Security=SSPI;"

#Ниже СКЛ-авторизация (для примера)
#"$ConnectionString="Data Source=(local);User=UserName;password=MyPassword;"

[void][reflection.assembly]::LoadWithPartialName("System.Data.Sql")
[void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")
[void][reflection.assembly]::LoadWithPartialName("System.Windows.Forms")

#Основной запрос к Серверу БД, для получения списка Job`ов
$Query=
"SELECT * FROM
(SELECT top 1 with ties
t1.name, t1.originating_server,
last_run_outcome=case t1.last_run_outcome
when 0 then 'Failed'
when 1 then 'Succeeded'
when 3 then 'Canceled'
when 5 then 'Unknown'
end,
enabled=case t1.enabled when 0 then 'NO' else 'YES' end,
current_execution_status=case t1.current_execution_status
when 1 then 'Executing'
when 2 then 'Waiting For Thread'
when 3 then 'Between Retries'
when 4 then 'Idle'
when 5 then 'Suspended'
when 6 then '[obsolete]'
when 7 then 'PerformingCompletion'
end,
t2.last_executed_step_date,
t2.next_scheduled_run_date,
t1.job_id
FROM OPENROWSET('SQLOLEDB','$ConnectionString','EXEC msdb..sp_help_job') t1
INNER JOIN msdb.dbo.sysjobactivity t2
ON t1.job_id=t2.job_id
ORDER BY row_number() over (partition by t1.job_id order by t2.job_history_id desc)
) t ORDER BY name
"

#Создаём коннекцию
$SQLConnection = new-object System.Data.SqlClient.SqlConnection($ConnectionString)
#Идентификатор сессии, изначально=0
$jobid=0

#Функция для заполнения таблицы Job`ов
function GetJobs
{
$SQLConnection.Open()
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($Query, $SQLConnection)
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLCommand)
$DataSet = New-Object System.Data.DataSet;
$SQLAdapter.Fill($DataSet);
$DataTable=New-Object System.Data.DataTable
$DataTable=$DataSet.Tables[0]
$reader=$SQLCommand.ExecuteReader()

$ListView.Items.Clear()
while ($reader.Read())
{
$item=new-object Windows.Forms.ListViewItem
$item.Text=$reader[0]
[void]$item.SubItems.Add($reader[1])
[void]$item.SubItems.Add($reader[2])
[void]$item.SubItems.Add($reader[3])
[void]$item.SubItems.Add($reader[4])
[void]$item.SubItems.Add($reader[5].ToString())
[void]$item.SubItems.Add($reader[6].ToString())
[void]$item.SubItems.Add($reader[7].ToString())

#Подкрашиваем красным строки у которых Job не выполнился
if ($reader[2] -eq "Failed")
{ $item.ForeColor="Red"}
[void]$ListView.Items.Add($item)
}

$SQLConnection.Close()
$ListView.Refresh()
#Время последнего считывания данных из БД потаймеру
$Time.Text="Last Update: $((Get-Date).ToString())"
}

#Функция для заполнения таблицы Step`ов
function GetHistory ($id)
{
$QueryHistory="SELECT
CASE run_date WHEN 0 THEN NULL ELSE
    convert(datetime,
            stuff(stuff(cast(run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +
            stuff(stuff(substring(cast(1000000 + run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),
            120) END AS [RunDate],
step_id, step_name,
run_status=case run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry (step only)'
when 3 then 'Canceled'
when 4 then 'In-progress message'
when 5 then 'Unknown'
end, message
FROM msdb.dbo.sysjobhistory
WHERE job_id='$id'
ORDER BY Instance_ID DESC"

$SQLConnection.Open()
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($QueryHistory, $SQLConnection)
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLCommand)
$DataSet = New-Object System.Data.DataSet;
$SQLAdapter.Fill($DataSet);
$DataTable=New-Object System.Data.DataTable
$DataTable=$DataSet.Tables[0]
$reader=$SQLCommand.ExecuteReader()

while ($reader.Read())
{
$item=new-object Windows.Forms.ListViewItem
$item.Text=$reader[0].ToString()
[void]$item.SubItems.Add($reader[1])
[void]$item.SubItems.Add($reader[2])
[void]$item.SubItems.Add($reader[3])
[void]$item.SubItems.Add($reader[4])

#StepID=0 выделяем жирным
if ($reader[1] -eq "0")
{
$titleFont = new-object System.Drawing.Font($this.Font,
[System.Drawing.FontStyle]::Bold)
$item.Font=$titleFont
}
#Подкрашиваем красным строки у которых Шаг закончился неудачей
if ($reader[3] -eq "Failed")
{
$item.ForeColor="Red"
}
[void]$ListViewHistory.Items.Add($item)
}
$SQLConnection.Close()
$ListViewHistory.Refresh()
}

$timer = new-object Windows.Forms.Timer
#Значение (в мс), через которое автоматически обновляется таблица (в данном случае раз в 60 секунд)
$timer.Interval=60000

#Создаём форму
$form = new-object Windows.Forms.Form
$form.Text = "Job Activity Monitor"
$form.WindowState="Maximized"
$form.startposition = "CenterScreen"
$form.autosize = 0 

#add a PanelJobs
$PanelJobs = new-object Windows.Forms.Panel
$PanelJobs.Location = New-Object System.Drawing.Size(0,0)
$PanelJobs.Dock="Fill"

#add a PanelSteps
$PanelSteps = new-object Windows.Forms.Panel
$PanelSteps.Location = New-Object System.Drawing.Size(0,200)
$PanelSteps.Height=300
$PanelSteps.Dock="Bottom"

#add a Scroll
$Scroll = new-object Windows.Forms.Splitter
$Scroll.Location = New-Object System.Drawing.Size(0,190)
$Scroll.Dock="Bottom"

#add a ListViewHistory
$ListViewHistory = new-object Windows.Forms.ListView
$ListViewHistory.Location = New-Object System.Drawing.Size(0,0)
$ListViewHistory.Dock="Fill"
$ListViewHistory.FullRowSelect="True"
$ListViewHistory.View="Details"
$ListViewHistory.Columns.Add("Date", 140)
$ListViewHistory.Columns.Add("Step ID", 60)
$ListViewHistory.Columns.Add("Step Name", 120)
$ListViewHistory.Columns.Add("Status", 80)
$ListViewHistory.Columns.Add("Message", 500)

#add a ListViewJobs
$ListView = new-object Windows.Forms.ListView
$ListView.Location = New-Object System.Drawing.Size(0,0)
$ListView.Dock="Fill"
$ListView.FullRowSelect="True"
$ListView.View="Details"
$ListView.Columns.Add("Name", 150)
$ListView.Columns.Add("Server", 100)
$ListView.Columns.Add("Last_Run_Outcome", 120)
$ListView.Columns.Add("Enabled", 80)
$ListView.Columns.Add("Status", 100)
$ListView.Columns.Add("Last Run", 120)
$ListView.Columns.Add("Next Run", 120)

#Выводим Шаги у выбранного Job`a
$ListView.Add_ItemSelectionChanged({
$ListViewHistory.Items.Clear()
$jobid=$this.items[$_.itemindex].SubItems[7].Text
GetHistory $jobid
}
)

#add a statusStrip
$statusStrip = new-object System.Windows.Forms.StatusStrip
$Time = new-object System.Windows.Forms.ToolStripStatusLabel
$Time.Text="Last Update: "
[void]$statusStrip.Items.add($Time)

#По таймеру обновляем табличку
$timer.add_Tick({GetJobs})
$timer.Start()

$form.controls.add($scroll)
$form.controls.add($PanelJobs)
$PanelSteps.controls.add($ListViewHistory)
$form.controls.add($PanelSteps)
$PanelJobs.controls.add($ListView)
$form.controls.add($statusStrip)

GetJobs
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

Блокирование (LOCK) Каждая транзакция запрашивает блокировку разных типов ресурсов, например строк, страниц или таблиц, от которых эта транзакция зависит.

Блокировка не дает другим транзакциям изменять ресурсы, чтобы избежать ошибок в транзакции, запросившей блокировку. Каждая транзакция освобождает свои блокировки, если больше не зависит от блокируемого ресурса.

Блокировки (особенно взаимоблокировки) - головная боль любого администратора, для их отслеживания написано не мало инструментов, в качестве примера работы с PowerShell предлагаю свой вариант утилиты для мониторинга блокировок.

#Строка коннекции к БД (в данном случае с Windows-авторизацией)
$ConnectionString="Data Source='(local)';Integrated Security=SSPI;"

#Ниже СКЛ-авторизация (для примера)
#Data Source='(local)';User='Login';password='Password';")

#Основной запрос к Серверу БД, для получения списка процессов
$Query=
"
select
spid,
blocked,
db_name(dbid) db,
cmd,
status,
hostname,
program_name,
loginame,
waittime, cpu, physical_io, MemUsage,
isnull((select text from sys.dm_exec_sql_text(sql_handle)),'') sql
from master.dbo.sysprocesses
--where blocked!=0
order by spid
"

#Создаём коннекцию
$SQLConnection = new-object System.Data.SqlClient.SqlConnection($ConnectionString)
#Идентификатор сессии, изначально=0
$spid=0

#Функция для заполнения таблицы
function GetProcesses
{
$SQLConnection.Open()
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($Query, $SQLConnection)
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SQLCommand)
$DataSet = New-Object System.Data.DataSet;
$SQLAdapter.Fill($DataSet);
$DataTable=New-Object System.Data.DataTable
$DataTable=$DataSet.Tables[0]
$reader=$SQLCommand.ExecuteReader()

$ListView.Items.Clear()
while ($reader.Read())
{
$item=new-object Windows.Forms.ListViewItem
$item.Text=$reader[0]
[void]$item.SubItems.Add($reader[1])
[void]$item.SubItems.Add($reader[2])
[void]$item.SubItems.Add($reader[3])
[void]$item.SubItems.Add($reader[4])
[void]$item.SubItems.Add($reader[5])
[void]$item.SubItems.Add($reader[6])
[void]$item.SubItems.Add($reader[7])
[void]$item.SubItems.Add($reader[8])
[void]$item.SubItems.Add($reader[9])
[void]$item.SubItems.Add($reader[10])
[void]$item.SubItems.Add($reader[11])
[void]$item.SubItems.Add($reader[12])
#Подкрашиваем красным строки у которых blocked<>0
if ($reader[1] -ne 0)
{ $item.ForeColor="Red"}
[void]$ListView.Items.Add($item)
}

$SQLConnection.Close()
$ListView.Refresh()
#Время последнего считывания данных из БД потаймеру
$Time.Text="Last Update: $((Get-Date).ToString())"
}

#Функция удаления сессии
function KillProcess ($id)
{
$QueryKill="kill $id"

$SQLConnection.Open()
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($QueryKill, $SQLConnection)
$SQLCommand.ExecuteNonQuery()
$SQLConnection.Close()
}

[void][reflection.assembly]::LoadWithPartialName("System.Data.Sql")
[void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")
[void][reflection.assembly]::LoadWithPartialName("System.Windows.Forms")
$timer = new-object Windows.Forms.Timer
#Значение (в мс), через которое автоматически обновляется таблица (в данном случае раз в 10 секунд)
$timer.Interval=10000

#Создаём форму
$form = new-object Windows.Forms.Form
$form.Text = "SQL Server Locks"
$form.WindowState="Maximized"
$form.startposition = "CenterScreen"
$form.autosize = 0 

#add a PanelLocs
$PanelLocks = new-object Windows.Forms.Panel
$PanelLocks.Location = New-Object System.Drawing.Size(0,0)
$PanelLocks.Dock="Fill"

#add a PanelView
$PanelView = new-object Windows.Forms.Panel
$PanelView.Location = New-Object System.Drawing.Size(0,200)
$PanelView.Height=100
$PanelView.Dock="Bottom"

#add a Scroll
$Scroll = new-object Windows.Forms.Splitter
$Scroll.Location = New-Object System.Drawing.Size(0,190)
$Scroll.Dock="Bottom"

#add a PanelButton
$PanelButton = new-object Windows.Forms.Panel
$PanelButton.Location = New-Object System.Drawing.Size(0,0)
$PanelButton.Width=100
$PanelButton.BorderStyle="Fixed3D"
$PanelButton.Dock="Right"

# add a TextBoxResult
$TextBoxResult = new-object Windows.Forms.TextBox
$TextBoxResult.Location = New-Object System.Drawing.Size(0,0)
$TextBoxResult.Multiline="true"
$TextBoxResult.Dock="Fill"
$TextBoxResult.ScrollBars="Vertical"

#add a button
$button = new-object Windows.Forms.button
$button.Location = New-Object System.Drawing.Size(5,5)
$button.text = "Kill"
$button.width = 90
#Удаляем сессию по нажатию на кнопку "Kill"
$button.add_click({
KillProcess $spid
GetProcesses
})

# add a TreeView
$ListView = new-object Windows.Forms.ListView
$ListView.Location = New-Object System.Drawing.Size(0,0)
$ListView.Dock="Fill"
$ListView.FullRowSelect="True"
$ListView.View="Details"
$ListView.Columns.Add("SPID", 50)
$ListView.Columns.Add("Blocked", 50)
$ListView.Columns.Add("DB", 60)
$ListView.Columns.Add("CMD", 100)
$ListView.Columns.Add("Status", 80)
$ListView.Columns.Add("HostName", 80)
$ListView.Columns.Add("ProgramName", 120)
$ListView.Columns.Add("LoginName", 100)
$ListView.Columns.Add("WaitTime", 80)
$ListView.Columns.Add("CPU", 80)
$ListView.Columns.Add("Physical IO", 80)
$ListView.Columns.Add("MemUsage", 80)
$ListView.Columns.Add("SQL", 200)

#Выводим текст запроса в выбранной сессии в таблице
$ListView.Add_ItemSelectionChanged({
$TextBoxResult.Clear()
$TextBoxResult.Text=$this.items[$_.itemindex].SubItems[12].Text
$spid=$this.items[$_.itemindex].Text
}
)

#add a statusStrip
$statusStrip = new-object System.Windows.Forms.StatusStrip
$Time = new-object System.Windows.Forms.ToolStripStatusLabel
$Time.Text="Last Update: "
[void]$statusStrip.Items.add($Time)

#По таймеру обновляем табличку
$timer.add_Tick({GetProcesses})
$timer.Start()

$form.controls.add($scroll)
$form.controls.add($PanelView)
$PanelView.controls.add($PanelButton)
$PanelView.controls.add($TextBoxResult)
$PanelButton.controls.add($button)
$form.controls.add($PanelLocks)
$PanelLocks.controls.add($ListView)
$form.controls.add($statusStrip)

GetProcesses
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

На днях пришлось навести порядок на нескольких старых серверах БД. Суть проблемы: в АД более 9500 учётных записей, активных не более 30% (остальные disabled).

Исторически сложилось так, что на серверах БД остались "висеть" и ряд учётных записей людей, которые покинули стены родного предприятия. В условия кризиса и не минуемой текучки персонала, скрипт написанный на PowerShell облегчит многим жизнь, в том числе и мне.

 

Для работы с AD я использую набор командлетов (часто называемых также AD cmdlets или QAD cmdlets), доступный с сайта http://www.quest.com/activeroles_server/arms.aspx.

$Domain = Read-Host "Введите адресс контролера"
$User = Read-Host "Введите логин"
$Password = Read-Host "Введите пароль" -AsSecureString;

#Функция очистки сервера БД от пользователей, которые в AD Disabled=TRUE

function DeleteLoginFromMSSQL ($Login)
{
#Подставляем к логину свой домен
$Login="Domain\$Login"
#Подключаемся к серверу, который чистим от учёток (в данном случае использую Windows-авторизацию)
$SQLConnection = new-object System.Data.SqlClient.SqlConnection(("Data Source='MyServerDB';Integrated Security=SSPI"))
#Сам запрос на очистку
$SQLQuery =
"DECLARE @Login sysname " +
" SET @Login='"+$Login+
#Чистим только если текущая отключенная учётка фигурирует на нашем сервере БД
"'IF EXISTS (SELECT * FROM SYS.SERVER_PRINCIPALS WHERE name=@Login) "+
"BEGIN" +
"DECLARE @str VARCHAR(255) "+
#Для более корректной очистки учёток, сперва удалим соответствующих им юзеров из всех баз
"SET @str=' "+
"USE ?; " +
"DECLARE @UserName sysname "+
"SELECT @UserName=t1.name FROM SYS.DATABASE_PRINCIPALS t1 INNER JOIN SYS.SERVER_PRINCIPALS t2 "+
"ON t1.sid=t2.sid where t2.name='''+@Login+''' "+
"IF @UserName IS NOT NULL "+
"EXECUTE sp_revokedbaccess @UserName; ' "+
#Вместо курсора по базам использую недокументированную функцию SP_MSFOREACHDB
"EXEC SP_MSFOREACHDB @str "+
#Удаляю саму учётку
"EXEC ('DROP LOGIN ['+@Login+']') " +
"END"

#Далее подключаюсь к серверу БД и выполняю запрос
$SQLConnection.Open()
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLQuery, $SQLConnection)
$SQLCommand.ExecuteNonQuery()
$SQLConnection.Close()
}

#Подключаемся к АД
Connect-QADService -Service $Domain -ConnectionAccount $User  -ConnectionPassword $Password;

#Пробегаем по всем отключенным пользователям из АД и вызываем функцию созданную выше
Get-QADUser -Disabled | foreach {DeleteLoginFromMSSQL $_.LogonName} 

#Отключение от АД
Disconnect-QADService;