Мониторинг SQL Server с помощью PowerShell. Часть 2. Мониторинг заданий SQL Server Agent

Monitoring SQL Server with PowerShell. Part 2: Monitor SQL Server Agent JobsДанный материал является переводом оригинальной статьи "MSSQLTips : Alejandro Cobar : SQL Server Agent Job Monitoring with PowerShell".

В продолжение предыдущей части, представляем сценарий PowerShell, реализующий сбор информации о заданиях SQL Server Agent для всех серверов, которые были зарегистрированы в рамках рассматриваемого решения по мониторингу SQL Server.

PS-скрипт подключится к каждому экземпляру SQL Server из таблицы inventory.MasterServerList и получит данные для каждого из этих экземпляров. Соединение с каждым экземпляром SQL Server будет основано на настройках из обозначенной таблицы. Соответственно, чтобы использовать этот модуль, предварительно необходимо создать основные объекты, описанные в предыдущей части.

 

Объекты базы данных

Для этого конкретного модуля будет создана только одна таблица, и это таблица для хранения информации о заданиях агента SQL Server из каждого экземпляра.

Опишем структуру таблицы, чтобы вы смогли получить представление о том, какие данные будут храниться. (Если вы хотите добавить больше полей в эту таблицу, обязательно настройте структуру в скрипте PowerShell и адаптируйте соответствующую логику, которая будет обрабатывать дополнительные столбцы.)

Таблица inventory.Jobs будет хранить информацию о заданиях агента SQL для всех серверов:

  • serverId - привязывается к inventory.MasterServerList
  • job_name - имя задания агента SQL
  • is_enabled - если задание включено
  • owner - владелец задания
  • date_created - когда задание было создано
  • date_modified - когда задание в последний раз изменено
  • frequency - как часто выполняется задание
  • days - как часто выполняется задание
  • execute_time - время последнего выполнения задания
  • data_collection_timestamp - когда данные были собраны в последний раз

 

Скрипт PowerShell

Сценарий PowerShell, который создает вышеуказанный объект и вставляет данные в таблицу inventory.Jobs называется Get-MSSQL-Instance-Jobs.ps1.

Содержимое скрипта:

Get-Content "C:\temp\Settings.ini" | foreach-object -begin {$h=@{}} -process { $k = [regex]::split($_,'='); if(($k[0].CompareTo("") -ne 0) -and ($k[0].StartsWith("[") -ne $True)) { $h.Add($k[0], $k[1]) } }
$server        = $h.Get_Item("centralServer")
$inventoryDB   = $h.Get_Item("inventoryDB")   if($server.length -eq 0){
    Write-Host "You must provide a value for the 'centralServer' in your Settings.ini file!!!" -BackgroundColor Red
    exit
}
if($inventoryDB.length -eq 0){
    Write-Host "You must provide a value for the 'inventoryDB' in your Settings.ini file!!!" -BackgroundColor Red
    exit
}   $mslExistenceQuery = "
SELECT Count(*) FROM dbo.sysobjects where id = object_id(N'[inventory].[MasterServerList]') and OBJECTPROPERTY(id, N'IsTable') = 1
"
$result = Invoke-Sqlcmd -Query $mslExistenceQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop   if($result[0] -eq 0){
    Write-Host "The table [inventory].[MasterServerList] wasn't found!!!" -BackgroundColor Red 
    exit
}   $enoughInstancesInMSLQuery = "
SELECT COUNT(*) FROM inventory.MasterServerList WHERE is_active = 1
"
$result = Invoke-Sqlcmd -Query $enoughInstancesInMSLQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop   if($result[0] -eq 0){
    Write-Host "There are no active instances registered to work with!!!" -BackgroundColor Red 
    exit
}   if ($h.Get_Item("username").length -gt 0 -and $h.Get_Item("password").length -gt 0) {
    $username   = $h.Get_Item("username")
    $password   = $h.Get_Item("password")
}   #Function to execute queries (depending on if the user will be using specific credentials or not)
function Execute-Query([string]$query,[string]$database,[string]$instance,[int]$trusted){
    if($trusted -eq 1){ 
        try{
            Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -ErrorAction Stop
        }
        catch{
            [string]$message = $_
            $errorQuery = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($instance)'),'Get-MSSQL-Instance-Jobs','"+$message.replace("'","''")+"',GETDATE())"
            Invoke-Sqlcmd -Query $errorQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
        }
    }
    else{
        try{
            Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -Username $username -Password $password -ErrorAction Stop
        }
        catch{
            [string]$message = $_
            $errorQuery = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($instance)'),'Get-MSSQL-Instance-Jobs','"+$message.replace("'","''")+"',GETDATE())"
            Invoke-Sqlcmd -Query $errorQuery -Database $inventoryDB -ServerInstance $server -ErrorAction Stop
        }
    }
}   ###############################
#Jobs inventory table creation#
###############################
$jobsInventoryTableQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[inventory].[Jobs]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [inventory].[Jobs](
    [serverId]                  [INT]NOT NULL,
    [job_name]                  [VARCHAR](128) NOT NULL,
    [is_enabled]                [TINYINT] NULL,
    [owner]                     [VARCHAR](32) NULL,
    [date_created]              [DATETIME] NULL,
    [date_modified]             [DATETIME] NULL,
    [frequency]                 [VARCHAR](32) NULL,
    [days]                      [VARCHAR](64) NULL,
    [execution_time]            [VARCHAR](64) NULL,
    [data_collection_timestamp] [DATETIME] NOT NULL   CONSTRAINT PK_JobsInventory PRIMARY KEY CLUSTERED (serverId,job_name),   CONSTRAINT FK_JobsInventory_MasterServerList FOREIGN KEY (serverId) REFERENCES inventory.MasterServerList(serverId) ON DELETE NO ACTION ON UPDATE NO ACTION,   ) ON [PRIMARY]
END
"
Execute-Query $jobsInventoryTableQuery $inventoryDB $server 1   #TRUNCATE the inventory.Jobs table to always store a fresh copy of the information from all the instances
Execute-Query "TRUNCATE TABLE inventory.Jobs" $inventoryDB $server 1   #Select the instances from the Master Server List that will be traversed
$instanceLookupQuery = "
SELECT
        serverId,
        trusted,
        CASE instance 
            WHEN 'MSSQLSERVER' THEN server_name                                   
            ELSE CONCAT(server_name,'\',instance)
        END AS 'instance',
        CASE instance 
            WHEN 'MSSQLSERVER' THEN ip                                   
            ELSE CONCAT(ip,'\',instance)
        END AS 'ip',
        CONCAT(ip,',',port) AS 'port'
FROM inventory.MasterServerList
WHERE is_active = 1
"
$instances = Execute-Query $instanceLookupQuery $inventoryDB $server 1   #For each instance, fetch the desired information
$jobsInformationQuery = "
SELECT
    SERVERPROPERTY('SERVERNAME') AS 'instance',
    sysjobs.name AS 'name',
    sysjobs.enabled AS 'enabled',
    SUSER_SNAME(sysjobs.owner_sid) AS 'owner',
    sysjobs.date_created AS 'date_created',
    sysjobs.date_modified AS 'date_modified',
    CASE
        WHEN freq_type = 4 THEN 'Daily'
    END AS 'frequency',
    'Every ' + CAST (freq_interval AS VARCHAR(3)) + ' day(s)' AS 'days',
    CASE
        WHEN freq_subday_type = 2 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
        + ' seconds ' + 'starting at '
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        WHEN freq_subday_type = 4 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
        + ' minutes ' + 'starting at '
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        WHEN freq_subday_type = 8 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
        + ' hours '   + 'starting at '
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        ELSE 'Starting at ' 
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
    END AS 'execution_time'
FROM msdb.dbo.sysjobs
JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE freq_type = 4   UNION   -- jobs with a weekly schedule
SELECT
    SERVERPROPERTY('SERVERNAME') AS 'instance',
    sysjobs.name AS 'name',
    sysjobs.enabled AS 'enabled',
    SUSER_SNAME(sysjobs.owner_sid) AS 'owner',
    sysjobs.date_created AS 'date_created',
    sysjobs.date_modified AS 'date_modified',
    CASE    
        WHEN freq_type = 8 THEN 'Weekly'
    END AS 'frequency',
    CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
    +CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
    +CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
    +CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
    +CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
    +CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
    +CASE WHEN freq_interval&1 = 1 THEN 'Sunday' ELSE '' END
    AS 'Days',
    CASE
        WHEN freq_subday_type = 2 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
        + ' seconds ' + 'starting at '
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') 
        WHEN freq_subday_type = 4 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
        + ' minutes ' + 'starting at '
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        WHEN freq_subday_type = 8 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
        + ' hours '   + 'starting at '
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        ELSE 'Starting at ' 
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
    END AS 'execution_time'
    FROM msdb.dbo.sysjobs
JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE freq_type = 8
UNION   -- jobs with a monthly schedule
SELECT
    SERVERPROPERTY('SERVERNAME') AS 'instance',
    sysjobs.name AS 'name',
    sysjobs.enabled AS 'enabled',
    SUSER_SNAME(sysjobs.owner_sid) AS 'owner',
    sysjobs.date_created AS 'date_created',
    sysjobs.date_modified AS 'date_modified',
    CASE    
        WHEN freq_type = 16 THEN 'Monthly'
    END AS 'frequency',
    CASE WHEN freq_interval&2 = 2 THEN 'Monday, ' ELSE '' END
    +CASE WHEN freq_interval&4 = 4 THEN 'Tuesday, ' ELSE '' END
    +CASE WHEN freq_interval&8 = 8 THEN 'Wednesday, ' ELSE '' END
    +CASE WHEN freq_interval&16 = 16 THEN 'Thursday, ' ELSE '' END
    +CASE WHEN freq_interval&32 = 32 THEN 'Friday, ' ELSE '' END
    +CASE WHEN freq_interval&64 = 64 THEN 'Saturday, ' ELSE '' END
    +CASE WHEN freq_interval&1 = 1 THEN 'Sunday' ELSE '' END
    AS 'Days',
    CASE
        WHEN freq_subday_type = 2 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
        + ' seconds ' + 'starting at '
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') 
        WHEN freq_subday_type = 4 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
        + ' minutes ' + 'starting at '
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        WHEN freq_subday_type = 8 THEN 'Every ' + CAST(freq_subday_interval AS VARCHAR(7)) 
        + ' hours '   + 'starting at '
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        ELSE 'Starting at ' 
        + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(active_start_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
    END AS 'execution_time'
FROM msdb.dbo.sysjobs
JOIN msdb.dbo.sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id
WHERE freq_type = 16
ORDER BY name
"   foreach ($instance in $instances){
   if($instance.trusted -eq 'True'){$trusted = 1}else{$trusted = 0}
   $sqlInstance = $instance.instance   #Go grab the complementary information for the instance
   Write-Host "Fetching jobs information from instance" $instance.instance   #Special logic for cases where the instance isn't reachable by name
   try{
        $results = Execute-Query $jobsInformationQuery "master" $sqlInstance $trusted
   }
   catch{
        $sqlInstance = $instance.ip
        [string]$message = $_
        $query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Jobs','"+$message.replace("'","''")+"',GETDATE())"
        Execute-Query $query $inventoryDB $server 1   try{  
            $results = Execute-Query $jobsInformationQuery "master" $sqlInstance $trusted
        }
        catch{
            $sqlInstance = $instance.port
            [string]$message = $_
            $query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Jobs','"+$message.replace("'","''")+"',GETDATE())"
            Execute-Query $query $inventoryDB $server 1   try{
                $results = Execute-Query $jobsInformationQuery "master" $sqlInstance $trusted
            }
            catch{
                [string]$message = $_
                $query = "INSERT INTO monitoring.ErrorLog VALUES("+$instance.serverId+",'Get-MSSQL-Instance-Jobs','"+$message.replace("'","''")+"',GETDATE())"
                Execute-Query $query $inventoryDB $server 1
            }
        }
   }   #Perform the INSERT in the inventory.Jobs only if it returns information
   if($results.Length -ne 0){   #Build the insert statement
      $insert = "INSERT INTO inventory.Jobs VALUES"
      foreach($result in $results){   
         $insert += "
         (
          '"+$instance.serverId+"',
          '"+$result['name']+"',
           "+$result['enabled']+",
          '"+$result['owner']+"',
          '"+$result['date_created']+"',
          '"+$result['date_modified']+"',
          '"+$result['frequency']+"',
          '"+$result['days']+"',
          '"+$result['execution_time']+"',
          GETDATE()
         ),
         "
       }   $insert = $insert -replace "''",'NULL'
       $insert = $insert -replace "NULLNULL",'NULL'
       Execute-Query $insert.Substring(0,$insert.LastIndexOf(',')) $inventoryDB $server 1
   }
}   Write-Host "Done!"

Сценарий включает действия, которые помогут вам проверить, отсутствуют ли некоторые ключевые элементы для успешного выполнения сценария. Например, он проверит, что таблица inventory.MasterServerList существует, и что в ней зарегистрирован, как минимум, 1 активный экземпляр, чтобы иметь возможность работать с чем-либо.

Сценарий PowerShell лучше хранить в централизованном месте. В нашем же примере мы используем локальный каталог "C:\temp".

Если вы хотите изменить рабочий каталог и использовать что-то отличное от "C:\temp", вам потребуется изменить первую строку скрипта, как показано ниже.

Execute path in Powershell script

 

Как пользоваться

Выполнять скрипт PowerShell можно одним из способов:

  • Щелкните правой кнопкой мыши Get-MSSQL-Instance-Jobs.ps1 и выберите "Run with PowerShell";
  • Откройте окно командной строки и перейдите в каталог, где размещён скрипт Get-MSSQL-Instance-Jobs.ps1 и указанные ранее файлы, и выполните команду вида:
    powershell "C:\temp\Get-MSSQL-Instance-Jobs.ps1"
  • Запланируйте это как задание SQL Server Agent для запуска сценария PowerShell;
  • Запланируйте это как задание планировщика задач Windows для запуска сценария PowerShell.

 

Проверка создания объектов базы данных

После запуска сценария PowerShell мы можем увидеть новый объект -  созданную таблицу inventory.Jobs.

New inventory.Jobs table in SQL Server monitoring database

 

Если мы сделаем запрос к таблице inventory.Jobs, мы увидим данные, которые были собраны.

Get SQL Server Agent Jobs from inventory.Jobs

Обратите внимание на то, что скрипт PowerShell будет сохранять только информацию с самого последнего выполнения. Если вы хотите сохранить информацию о предыдущих выполнениях, вам придется изменить сценарий и адаптировать его к вашему конкретному варианту использования.

 

Поиск заданий с ошибками

Чтобы проверить наличие ошибок, опросите таблицу monitor.ErrorLog, используя следующий запрос:

SELECT * FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Jobs'

Если вы хотите получить экземпляр SQL Server с ошибками, выполните запрос следующим образом:

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE 
   CONCAT(msl.server_name,'\',msl.instance) 
   END AS instance,  
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-Instance-Jobs'

 

Полезные запросы

Собрав все данные, связанные с заданиями во всех ваших экземплярах, вы можете получить ответы на ряд интересных вопросов.

Найти задания, которые включают резервное копирование.

Каков график резервного копирования во всех экземплярах. Здесь мы ищем ключевое слово "Backup" в названии задания.

SELECT
   CASE WHEN msl.instance = 'MSSQLSERVER' 
   THEN msl.server_name 
   ELSE CONCAT(msl.server_name,'\',msl.instance) 
   END AS instance, 
   j.job_name,
   j.frequency,
   j.days,
   j.execution_time
FROM inventory.Jobs j
JOIN inventory.MasterServerList msl ON j.serverId = msl.serverId
WHERE j.job_name LIKE '%Backup%'

 Найти задания с владельцем, отличным от 'sa'.

Я хотел бы знать, какие задания имеют владельцев,отличных от 'sa'. Здесь мы смотрим на столбец owner.

SELECT
   CASE WHEN msl.instance = 'MSSQLSERVER' 
   THEN msl.server_name ELSE         
   CONCAT(msl.server_name,'\',msl.instance) 
   END AS instance,
   j.job_name,
   j.owner
FROM inventory.Jobs j
JOIN inventory.MasterServerList msl ON j.serverId = msl.serverId
WHERE j.owner <> 'sa'

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