Ваш журнальчик всё

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

Впрочем, этот скрипт может сообщать о превышении порогового значения журнала транзакций любой базы данных, живущей на MSSQL. Всякое бывает — может быть кому-то данный метод будет полезен, если нет возможности или желания разворачивать Zabbix (тег) и постоянно смотреть на графики заполняемого пространства.

Теперь про принцип работы скрипта. В самом начале скрипт проходит авторизацию на сервере СУБД и запрашивает данные о состоянии журнала транзакций и максимально допустимом размере, далее сравнивает текущий размер журнала с максимальным пределом. Если текущий размер журнала равен или превышает пороговое значение (переменная $threshold) — отправляется письмо на указанный адрес электронной почты (обратите внимание на переменные $sendmailserver, $sendmailaddress, $sendmailsubject). За отправку письма отвечает функция Send-Mail.

Тестирование работы скрипта проводились только в рамках почтового сервера MS Exchange 2016 (тег). Для других почтовых серверов (вероятно) придётся пройтись по коду киянкой и напильником.

В коде нужно отредактировать блок с параметрами подключения и переменные с данными для вызова функции Send-Mail. Каждая переменная снабжена комментарием. Думаю, не заплутаете.

Собственно, код:

# Заголовок скрипта #
[System.Console]::Title = "Состояние журнала транзакций базы данных"

# Параметры подключения к базе данных #
$server = "SQLSERVER" # <= Имя сервера баз данных
$database = "RDSFARM" # <= Имя базы данных
$user = "sa" # <= Имя пользователя
$password = "VeRyStroNGpassworD" # <= Пароль

# Пороговое значение для уведомления (в %) #
$threshold = 90

# Функция отправки письма #
function Send-Mail 
{
    param 
    (
        [string]$MailServer,
        [string]$MailFrom,
        [string]$MailTo,
        [string]$MailSubject,
        [string]$MailBody
    )

    try 
    {
        # Создание сообщения #
        $message = New-Object System.Net.Mail.MailMessage $MailFrom, $MailTo
        $message.Subject = $MailSubject
        $message.Body = $MailBody

        # Создание клиента #
        $smtp = New-Object Net.Mail.SmtpClient($MailServer)

        # Отправка письма #
        $smtp.Send($message)

        Write-Host -ForegroundColor Green "Письмо успешно отправлено на $MailTo!"
    } 
    catch
    {
        Write-Host -ForegroundColor Red "Ошибка при отправке письма: $_"
    }
}

# Строка подключения #
$connectionString = "Server=$server;Database=$database;User Id=$user;Password=$password;"

# Создаем SQL-соединение #
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()

# SQL-запрос для получения информации о размере файла журнала с учетом максимального размера и темпа роста #
$sql = @"
SELECT
    DB_NAME(u.database_id) AS [DatabaseName],
    u.total_log_size_in_bytes / (1024 * 1024) AS [TotalLogSizeMB],
    u.used_log_space_in_bytes / (1024 * 1024) AS [UsedLogSizeMB],
    (u.total_log_size_in_bytes - u.used_log_space_in_bytes) / (1024 * 1024) AS [FreeLogSizeMB],
    (CAST(u.used_log_space_in_bytes AS FLOAT) / u.total_log_size_in_bytes) * 100 AS [LogUsagePercent],
    mf.size / 128 AS [MaxLogFileSizeMB],  -- Максимальный размер файла журнала в МБ
    mf.max_size / 128 AS [MaxLogFileGrowthMB]  -- Максимальный размер файла при росте, если задан
FROM
    sys.dm_db_log_space_usage AS u
JOIN
    sys.master_files AS mf ON u.database_id = mf.database_id
WHERE
    mf.type_desc = 'LOG'  -- Ограничиваем выборку только файлами журнала
"@

# Выполнение запроса #
$command = New-Object System.Data.SqlClient.SqlCommand($sql, $connection)
$reader = $command.ExecuteReader()

# Чтение результатов #
while ($reader.Read())
{
    # Присвоение значения переменным #
    $databaseName = $reader["DatabaseName"].ToString()
    $totalLogSize = [double]$reader["TotalLogSizeMB"]
    $usedLogSize = [double]$reader["UsedLogSizeMB"]
    $freeLogSize = [double]$reader["FreeLogSizeMB"]
    $logUsagePercent = [math]::Round([double]$reader["LogUsagePercent"])
    $maxLogFileSize = [double]$reader["MaxLogFileSizeMB"]
    $maxLogFileGrowth = [double]$reader["MaxLogFileGrowthMB"]

    # Вывод сообщения в консоль #
    Write-Host -ForegroundColor Green "База данных: $databaseName"
    Write-Host -ForegroundColor Yellow "------------------------------------"
    Write-Host "Полный размер журнала: $totalLogSize MB"
    Write-Host "Используемый размер журнала: $usedLogSize MB"
    Write-Host "Свободный размер журнала: $freeLogSize MB"
    Write-Host "Использование журнала: $logUsagePercent%"
    Write-Host "Максимальный размер файла журнала: $maxLogFileSize MB"
    Write-Host "Максимальный размер роста файла журнала: $maxLogFileGrowth MB"
    Write-Host -ForegroundColor Yellow "------------------------------------"
    Write-Host -ForegroundColor Cyan "Текущее пороговое значение: $threshold%"

    # Если процент использования журнала больше или равен предельному значению #
    if ($logUsagePercent -ge $threshold) 
    {
        # Вывод сообщения в консоль #
        $reportalarm = "ВНИМАНИЕ! Журнал транзакций базы данных $databaseName заполнен на $logUsagePercent%! В настоящее время занято $usedLogSize MB из $totalLogSize MB. Максимальный размер лога при росте составляет $maxLogFileGrowth MB."
        Write-Host
        Write-Host $reportalarm -ForegroundColor Red
        Write-Host
        
        # Отправка письма #
        $sendmailserver = "192.168.1.100" # <= IP-адрес почтового сервера
        $sendmailaddress = "admin@domain.ru" # <= почта отправителя/получателя
        $sendmailsubject = "$databaseName - переполнение журнала транзакций!" # <= тема письма

        # Вызов функции отправки письма #
        Send-Mail -MailServer $sendmailserver `
                  -MailFrom $sendmailaddress `
                  -MailTo $sendmailaddress `
                  -MailSubject $sendmailsubject `
                  -MailBody $reportalarm
    }
}

# Закрываем соединение #
$reader.Close()
$connection.Close()

К коду не прикручен обработчик ошибок и условие отсутствия связи с сервером СУБД. Подразумевается, что подключение должно пройти без сучка и задоринки. На этапе отладки можете добавить блок try-catch сами.

Вот так выглядит пример работы скрипта с обращением к базе данных терминальной фермы:

Обратите отдельное внимание на максимальный размер роста файла журнала транзакций. В данном случае он равен 31 Гб и при отправке отчёта нужно обращать внимание на данные полного размера журнала и используемого, соответственно. Этот момент критичен для принятия своевременных диагностических действий по отношению к журналу базы данных. При желании можно поднять пороговое значение, например, до 95 процентов.

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

-- Подключаемся к базе данных RDSFARM
USE RDSFARM;
GO

-- Устанавливаем режим восстановления базы данных на SIMPLE (простой)
ALTER DATABASE RDSFARM
SET RECOVERY SIMPLE;
GO

-- Уменьшаем размер файла журнала транзакций.
-- Первый параметр - имя файла журнала, второй - желаемый размер в МБ.
DBCC SHRINKFILE (RDSFARM_log, 1024);

-- Возвращаем режим восстановления базы данных на FULL (полный)
ALTER DATABASE RDSFARM
SET RECOVERY FULL;
GO

Метод работает только если база данных находится вне кластера высокой доступности.

Только не забудьте заменить RDSFARM на имя целевой базы данных и проследить, чтобы имя RDSFARM_log также ссылалось на корректное имя журнала.