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

Подготовка среды

Прежде чем мы начнем, убедитесь, что у вас установлен PowerShell на вашей системе. Вы можете запустить PowerShell, нажав кнопку “Пуск” и введя “PowerShell” в поле поиска. Выберите либо Windows PowerShell ISE (графический интерфейс), либо Windows PowerShell (командная строка) в зависимости от ваших предпочтений.

По умолчанию, политика выполнения в PowerShell установлена на “Restricted”, что означает, что скрипты не будут выполняться. Чтобы изменить политику выполнения, откройте PowerShell и введите команду Set-ExecutionPolicy RemoteSigned. Это позволит вам запускать собственные скрипты и скрипты из Интернета, подписанные доверенным издателем.

Мониторинг роста баз данных

Теперь, когда у нас настроена среда, давайте перейдем к мониторингу роста баз данных SQL Server. Следующий скрипт PowerShell читает текстовый файл, содержащий список экземпляров SQL Server. Для каждого экземпляра он извлекает имя файла, путь к файлу, размер файла и использованное пространство как для файла данных (.mdf), так и для файла журнала (.ldf) каждой базы данных. Затем данные записываются в электронную таблицу Excel.

# Создание нового объекта Excel с использованием COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
$intRow = 1

ForEach ($instance in Get-Content "C:\Users\dkelly\Documents\PowershellScripts\sqlservers.txt")
{
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
    $dbs = $s.Databases

    $intRow++
    # Создание заголовков столбцов
    $Sheet.Cells.Item($intRow,1) = "Сервер: $s"
    $Sheet.Cells.Item($intRow,1).Font.Size = 12
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True

    $intRow++
    $Sheet.Cells.Item($intRow,2) = "База данных"
    $Sheet.Cells.Item($intRow,2).Font.Bold = $True
    $Sheet.Cells.Item($intRow,3) = "Имя данных"
    $Sheet.Cells.Item($intRow,3).Font.Bold = $True
    $Sheet.Cells.Item($intRow,4) = "Файл данных"
    $Sheet.Cells.Item($intRow,4).Font.Bold = $True
    $sheet.Cells.Item($intRow,5) = "Размер данных (МБ)"
    $Sheet.Cells.Item($intRow,5).Font.Bold = $True
    $Sheet.Cells.Item($intRow,6) = "Использованное пространство данных (МБ)"
    $Sheet.Cells.Item($intRow,6).Font.Bold = $True
    $Sheet.Cells.Item($intRow,7) = "Имя журнала"
    $Sheet.Cells.Item($intRow,7).Font.Bold = $True
    $Sheet.Cells.Item($intRow,8) = "Размер журнала (МБ)"
    $Sheet.Cells.Item($intRow,8).Font.Bold = $True
    $Sheet.Cells.Item($intRow,9) = "Использованное пространство журнала (МБ)"
    $Sheet.Cells.Item($intRow,9).Font.Bold = $True
    $Sheet.Cells.Item($intRow,10) = "Файл журнала"
    $Sheet.Cells.Item($intRow,10).Font.Bold = $True

    foreach ($db in $dbs)
    {
        $dbname = $db.Name
        $fileGroups = $db.FileGroups

        ForEach ($fg in $fileGroups)
        {
            If ($fg)
            {
                $intRow++

                $mdfInfo = $fg.Files | Select Name, FileName, size, UsedSpace
                $Sheet.Cells.Item($intRow,2) = $dbname
                $Sheet.Cells.Item($intRow,3) = $mdfInfo.Name
                $Sheet.Cells.Item($intRow,4) = $mdfInfo.FileName
                $Sheet.Cells.Item($intRow,5) = ($mdfInfo.size / 1000)
                $Sheet.Cells.Item($intRow,6) = ($mdfInfo.UsedSpace / 1000)

                $logInfo = $db.LogFiles | Select Name, FileName, Size, UsedSpace
                $Sheet.Cells.Item($intRow,7) = $logInfo.Name
                $Sheet.Cells.Item($intRow,8) = ($logInfo.Size / 1000)
                $Sheet.Cells.Item($intRow,9) = ($logInfo.UsedSpace / 1000)
                $Sheet.Cells.Item($intRow,10) = $logInfo.FileName
            }
        }
    }
    $intRow++
}

$Sheet.UsedRange.EntireColumn.AutoFit()

Скрипт использует сборку Microsoft.SqlServer.SMO для подключения к каждому экземпляру SQL Server и извлечения необходимой информации. Полученные данные затем записываются в электронную таблицу Excel.

Заключение

Мониторинг роста баз данных SQL Server является важным для поддержания оптимальной производительности и планирования будущих потребностей в хранении данных. С помощью PowerShell мы легко можем собирать и сохранять исторические данные о росте баз данных. Запустив предоставленный скрипт, вы сможете отслеживать размер файла, путь к файлу и использованное пространство как для файлов данных, так и для файлов журнала для нескольких экземпляров SQL Server. Эта информация может быть бесценной для планирования мощности и устранения неполадок.

Следите за нашим следующим советом, где мы рассмотрим, как взять эти данные и сохранить их в базе данных SQL с использованием SSIS для дальнейшего анализа и отчетности.