Описание:
Представляю вашему вниманию PowerShell-скрипт для автоматизации сложной процедуры переноса индексов между файловыми группами в Microsoft SQL Server.
Скрипт решает задачу пересоздания индексов в целевой файловой группе (или ФГ по умолчанию) с учетом всех «подводных камней»: он автоматически пропускает индексы, связанные с полнотекстовым поиском или XML-индексами, создаёт резервные копии (скрипты CREATE) для каждого изменяемого индекса и ведёт детальный лог как в файловой системе, так и в таблице базы данных.
Ключевые возможности:
-
Избирательность: Пересоздаёт только те индексы, которые физически находятся не в целевой файловой группе.
-
Безопасность: Автоматическое создание
CREATE-скриптов для быстрого отката. Очистка старых бэкапов (>7 дней). -
Интеллектуальность: Пропуск индексов с ограничениями (FullText, XML, Spatial) — они требуют ручного вмешательства.
-
Прозрачность: Детальное логирование в файл и в SQL-таблицу
DBATools.dbo.CheckProjectTables_logс фиксацией времени начала/окончания и ошибок. -
Контроль: Режим
PreviewOnlyдля генерации скрипта без выполнения, а также финальная проверка расположения всех индексов после завершения работы. -
Гибкость: Возможность обработки всех или только некластерных индексов, выборочная обработка таблиц.
<#
.SYNOPSIS
Пересоздание индексов в новой файловой группе с полным логированием и резервным копированием
.DESCRIPTION
Скрипт автоматически переносит индексы в указанную файловую группу с учётом всех ограничений:
- Если TargetFileGroup пустой = используется ФГ по умолчанию для каждой БД
- Пересоздаются ТОЛЬКО индексы, не находящиеся в целевой ФГ
- Бэкап создаётся ТОЛЬКО для индексов, которые будут пересоздаваться
- Индексы с ограничениями (полнотекстовый ключ, XML) пропускаются с предупреждением
- Полное логирование в файл и в таблицу DBATools.dbo.CheckProjectTables_log
- Автоматическая очистка старых логов и бэкапов (>7 дней)
- Финальная проверка всех индексов после выполнения
#>
# ============================================================================
# ПАРАМЕТРЫ КОНФИГУРАЦИИ (ОТРЕДАКТИРУЙТЕ ПОД СВОИ НУЖДЫ)
# ============================================================================
<#
SqlInstance = Имя или адрес SQL Server экземпляра
TargetFileGroup = Имя целевой файловой группы. ПУСТО = использовать ФГ по умолчанию для каждой БД
Databases = Список баз данных для обработки (через запятую)
ExcludeDatabases = Список исключаемых баз данных (системные по умолчанию)
Tables = Список таблиц для обработки (пусто = все таблицы)
RecreateUnconditionally= $true = пересоздать ВСЕ индексы, $false = только при несоответствии ФГ
NonClusteredOnly = $true = обрабатывать только некластерные индексы
PreviewOnly = $true = только показать команды без выполнения
LogPath = Путь для сохранения лог-файлов
LogRetentionDays = Количество дней хранения лог-файлов (автоочистка)
BackupIndexes = $true = создавать резервные копии индексов перед пересозданием
BackupPath = Путь для сохранения бэкапов индексов
BackupRetentionDays = Количество дней хранения бэкапов (автоочистка)
LogDatabase = База данных для логирования операций
LogTable = Таблица для логирования операций
#>
$Config = @{
SqlInstance = "-db13"
TargetFileGroup = "" # Пусто = использовать ФГ по умолчанию для каждой БД
Databases = @("MSK-DBCL01`$msk-dbag01_") # Экранирование $ в имени БД
ExcludeDatabases = @("master", "model", "msdb", "tempdb")
Tables = @()
RecreateUnconditionally = $false # $false = пересоздавать только при несоответствии ФГ
NonClusteredOnly = $false
PreviewOnly = $false
LogPath = "C:\temp\IndexRebuild"
LogRetentionDays = 7
BackupIndexes = $true
BackupPath = "C:\temp\IndexRebuild\Backups"
BackupRetentionDays = 7
LogDatabase = "DBATools"
LogTable = "dbo.CheckProjectTables_log"
}
# ============================================================================
# ФУНКЦИЯ ЛОГИРОВАНИЯ С ЦВЕТОВЫМ ВЫВОДОМ
# ============================================================================
function Write-Log {
param(
[Parameter(Mandatory=$true)]
[string]$Message,
[ValidateSet("INFO", "WARN", "ERROR", "SUCCESS")]
[string]$Level = "INFO",
[string]$LogFile
)
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
$logEntry = "[$timestamp] [$Level] $Message"
# Цвета для консоли
$color = switch ($Level) {
"INFO" { "Cyan" }
"WARN" { "Yellow" }
"ERROR" { "Red" }
"SUCCESS" { "Green" }
default { "White" }
}
Write-Host $logEntry -ForegroundColor $color
# Запись в файл лога
if ($LogFile) {
try {
$logEntry | Out-File -FilePath $LogFile -Append -Encoding utf8 -ErrorAction Stop
} catch {
Write-Warning "Не удалось записать в лог-файл: $_"
}
}
}
# ============================================================================
# ИНИЦИАЛИЗАЦИЯ ЛОГИРОВАНИЯ И ОЧИСТКА СТАРЫХ ФАЙЛОВ
# ============================================================================
function Initialize-Logging {
param([string]$BasePath)
# Создание директории для логов
if (-not (Test-Path $BasePath)) {
New-Item -Path $BasePath -ItemType Directory -Force | Out-Null
Write-Log "Создана директория для логов: $BasePath" -Level "INFO"
}
# Очистка старых логов
$cutoffDate = (Get-Date).AddDays(-$Config.LogRetentionDays)
$oldLogs = Get-ChildItem -Path $BasePath -Filter "*.log" -File |
Where-Object { $_.LastWriteTime -lt $cutoffDate }
if ($oldLogs.Count -gt 0) {
Write-Log "Очистка старых логов (старше $($Config.LogRetentionDays) дней)..." -Level "INFO"
$deletedCount = 0
foreach ($log in $oldLogs) {
try {
Remove-Item -Path $log.FullName -Force -ErrorAction Stop
$deletedCount++
} catch {
Write-Log "Не удалось удалить $($_.Name): $_" -Level "WARN"
}
}
Write-Log "Удалено $deletedCount старых лог-файлов" -Level "INFO"
}
# Создание нового лог-файла
$logFileName = "IndexRebuild_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"
$logFilePath = Join-Path -Path $BasePath -ChildPath $logFileName
Write-Log "Логирование в файл: $logFilePath" -Level "INFO" -LogFile $logFilePath
return $logFilePath
}
# ============================================================================
# ОЧИСТКА СТАРЫХ БЭКАПОВ ИНДЕКСОВ
# ============================================================================
function Cleanup-OldBackups {
param(
[string]$BackupPath,
[int]$RetentionDays,
[string]$LogFile
)
if (-not (Test-Path $BackupPath)) {
Write-Log "Папка бэкапов не найдена: $BackupPath" -Level "WARN" -LogFile $LogFile
return
}
$cutoffDate = (Get-Date).AddDays(-$RetentionDays)
$oldBackups = Get-ChildItem -Path $BackupPath -Filter "*.sql" -File |
Where-Object { $_.LastWriteTime -lt $cutoffDate }
if ($oldBackups.Count -gt 0) {
Write-Log "Очистка старых бэкапов (старше $RetentionDays дней)..." -Level "INFO" -LogFile $LogFile
$deletedCount = 0
foreach ($backup in $oldBackups) {
try {
Remove-Item -Path $backup.FullName -Force -ErrorAction Stop
$deletedCount++
Write-Log "Удалён бэкап: $($backup.Name)" -Level "INFO" -LogFile $LogFile
} catch {
Write-Log "Не удалось удалить бэкап $($backup.Name): $_" -Level "WARN" -LogFile $LogFile
}
}
Write-Log "Удалено $deletedCount старых бэкапов" -Level "INFO" -LogFile $LogFile
} else {
Write-Log "Старые бэкапы для удаления не найдены" -Level "INFO" -LogFile $LogFile
}
}
# ============================================================================
# РЕЗЕРВНОЕ КОПИРОВАНИЕ ИНДЕКСОВ (ТОЛЬКО ДЛЯ ИНДЕКСОВ, КОТОРЫЕ БУДУТ ПЕРЕСОЗДАВАТЬСЯ)
# ============================================================================
function Backup-Indexes {
param(
[string]$SqlInstance,
[hashtable]$IndexesToBackup,
[string]$BackupPath,
[string]$LogFile,
[string]$LogDatabase,
[string]$LogTable
)
Write-Log "Начало создания резервных копий индексов..." -Level "INFO" -LogFile $LogFile
if (-not (Test-Path $BackupPath)) {
New-Item -Path $BackupPath -ItemType Directory -Force | Out-Null
}
$totalBackups = 0
$server = $null
try {
$server = Connect-DbaInstance -SqlInstance $SqlInstance -ErrorAction Stop
} catch {
Write-Log "Не удалось подключиться для логирования бэкапов: $_" -Level "ERROR" -LogFile $LogFile
}
foreach ($dbName in $IndexesToBackup.Keys) {
$indexes = $IndexesToBackup[$dbName]
if (-not $indexes -or $indexes.Count -eq 0) {
Write-Log "В БД $dbName нет индексов для бэкапа" -Level "INFO" -LogFile $LogFile
continue
}
Write-Log "Создание бэкапа для $($indexes.Count) индексов в БД: $dbName" -Level "INFO" -LogFile $LogFile
foreach ($idx in $indexes) {
try {
# Генерация скрипта для одного индекса
$createCommand = ""
if ($idx.is_primary_key -or $idx.is_unique_constraint) {
$constraintType = if ($idx.is_primary_key) { "PRIMARY KEY" } else { "UNIQUE" }
$createCommand = "ALTER TABLE [$($idx.SchemaName)].[$($idx.TableName)] " +
"ADD CONSTRAINT [$($idx.IndexName)] $constraintType $($idx.IndexType)($($idx.KeyColumns))"
if (![string]::IsNullOrWhiteSpace($idx.IncludedColumns)) {
$createCommand += " INCLUDE ($($idx.IncludedColumns))"
}
if ($idx.has_filter) {
$createCommand += " WHERE $($idx.filter_definition)"
}
if ($idx.fill_factor -gt 0 -and $idx.fill_factor -ne 80) {
$createCommand += " WITH (FILLFACTOR = $($idx.fill_factor))"
}
$createCommand += " ON [$($idx.FileGroupName)];"
} else {
$isUnique = if ($idx.is_unique) { "UNIQUE " } else { "" }
$createCommand = "CREATE ${isUnique}$($idx.IndexType) INDEX [$($idx.IndexName)] " +
"ON [$($idx.SchemaName)].[$($idx.TableName)]($($idx.KeyColumns))"
if (![string]::IsNullOrWhiteSpace($idx.IncludedColumns)) {
$createCommand += " INCLUDE ($($idx.IncludedColumns))"
}
if ($idx.has_filter) {
$createCommand += " WHERE $($idx.filter_definition)"
}
if ($idx.fill_factor -gt 0 -and $idx.fill_factor -ne 80) {
$createCommand += " WITH (FILLFACTOR = $($idx.fill_factor))"
}
$createCommand += " ON [$($idx.FileGroupName)];"
}
# Сохранение в отдельный файл
$safeIndexName = $idx.IndexName -replace '[\\/:*?"<>|]', '_'
$backupFile = Join-Path -Path $BackupPath -ChildPath "${dbName}_${idx.SchemaName}_${idx.TableName}_${safeIndexName}_Backup_$(Get-Date -Format 'yyyyMMdd_HHmmss').sql"
$createCommand | Out-File -FilePath $backupFile -Encoding utf8 -Force
$totalBackups++
Write-Log "✓ Бэкап сохранён: $backupFile" -Level "SUCCESS" -LogFile $LogFile
# Логирование в БД
if ($server -and $LogDatabase) {
try {
$logQuery = @"
INSERT INTO $LogTable
(DatabaseName, SchemaName, TableName, IndexName, Command, CommandType, CommandSubType, StartTime)
VALUES (@db, @schema, @table, @index, @cmd, 'BACKUP', 'Backup', GETDATE());
"@
$null = Invoke-DbaQuery -SqlInstance $server -Database $LogDatabase -Query $logQuery -SqlParameter @{
db = $dbName
schema = $idx.SchemaName
table = $idx.TableName
index = $idx.IndexName
cmd = $createCommand
} -ErrorAction Stop
} catch {
Write-Log "Не удалось записать бэкап в лог БД: $_" -Level "WARN" -LogFile $LogFile
}
}
} catch {
Write-Log "✗ Ошибка при создании бэкапа для индекса $($idx.IndexName): $_" -Level "ERROR" -LogFile $LogFile
}
}
}
Write-Log "Резервное копирование завершено. Создано $totalBackups файлов" -Level "SUCCESS" -LogFile $LogFile
return $totalBackups
}
# ============================================================================
# ПРОВЕРКА ИНДЕКСОВ В ФАЙЛОВОЙ ГРУППЕ (ФИНАЛЬНАЯ ПРОВЕРКА)
# ============================================================================
function Check-IndexesInFileGroup {
param(
[string]$SqlInstance,
[string[]]$Databases,
[hashtable]$TargetFileGroups,
[string]$LogFile
)
Write-Log "=== ФИНАЛЬНАЯ ПРОВЕРКА ИНДЕКСОВ ===" -Level "INFO" -LogFile $LogFile
$totalIndexes = 0
$indexesInTargetFG = 0
$indexesNotInTargetFG = @()
foreach ($dbName in $Databases) {
$targetFG = $TargetFileGroups[$dbName]
try {
$checkQuery = @"
SELECT
s.name AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
ds.name AS FileGroupName,
i.type_desc AS IndexType,
i.is_primary_key,
i.is_unique_constraint
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE i.index_id > 0
AND i.is_disabled = 0
AND t.is_ms_shipped = 0
AND t.name <> 'sysdiagrams'
ORDER BY s.name, t.name, i.name;
"@
$indexes = Invoke-DbaQuery -SqlInstance $SqlInstance -Database $dbName -Query $checkQuery -ErrorAction Stop
$dbTotal = $indexes.Count
$dbInTarget = ($indexes | Where-Object { $_.FileGroupName -eq $targetFG }).Count
$dbNotInTarget = $dbTotal - $dbInTarget
Write-Log "БД: $dbName - Всего индексов: $dbTotal, В целевой ФГ ($targetFG): $dbInTarget, Не в целевой ФГ: $dbNotInTarget" -Level "INFO" -LogFile $LogFile
$totalIndexes += $dbTotal
$indexesInTargetFG += $dbInTarget
if ($dbNotInTarget -gt 0) {
$notInTarget = $indexes | Where-Object { $_.FileGroupName -ne $targetFG }
foreach ($idx in $notInTarget) {
$indexesNotInTargetFG += [PSCustomObject]@{
DatabaseName = $dbName
SchemaName = $idx.SchemaName
TableName = $idx.TableName
IndexName = $idx.IndexName
CurrentFileGroup = $idx.FileGroupName
TargetFileGroup = $targetFG
IndexType = $idx.IndexType
IsPrimaryKey = $idx.is_primary_key
IsUniqueConstraint = $idx.is_unique_constraint
}
}
}
} catch {
Write-Log "Ошибка при проверке индексов в БД $dbName : $_" -Level "ERROR" -LogFile $LogFile
}
}
Write-Log "=== ИТОГИ ПРОВЕРКИ ===" -Level "INFO" -LogFile $LogFile
Write-Log "Всего индексов: $totalIndexes" -Level "INFO" -LogFile $LogFile
Write-Log "Индексов в целевой ФГ: $indexesInTargetFG" -Level "INFO" -LogFile $LogFile
Write-Log "Индексов НЕ в целевой ФГ: $($indexesNotInTargetFG.Count)" -Level "INFO" -LogFile $LogFile
if ($indexesNotInTargetFG.Count -gt 0) {
Write-Log "Список индексов, которые НЕ были перенесены в целевую ФГ:" -Level "WARN" -LogFile $LogFile
$indexesNotInTargetFG | ForEach-Object {
Write-Log " $($_.DatabaseName).$($_.SchemaName).$($_.TableName).$($_.IndexName) - Текущая ФГ: $($_.CurrentFileGroup)" -Level "WARN" -LogFile $LogFile
}
# Сохранение в файл
$notInTargetFile = Join-Path -Path $Config.LogPath -ChildPath "IndexesNotInTargetFG_$(Get-Date -Format 'yyyyMMdd_HHmmss').txt"
$indexesNotInTargetFG | Format-Table -AutoSize | Out-File -FilePath $notInTargetFile -Encoding utf8
Write-Log "Список сохранён в файл: $notInTargetFile" -Level "WARN" -LogFile $LogFile
} else {
Write-Log "✓ ВСЕ индексы успешно перенесены в целевую файловую группу!" -Level "SUCCESS" -LogFile $LogFile
}
return $indexesNotInTargetFG
}
# ============================================================================
# ГЛАВНЫЙ СКРИПТ
# ============================================================================
# Инициализация логирования
$logFilePath = Initialize-Logging -BasePath $Config.LogPath
Write-Log "=== НАЧАЛО ПЕРЕСОЗДАНИЯ ИНДЕКСОВ ===" -Level "INFO" -LogFile $logFilePath
Write-Log "Сервер: $($Config.SqlInstance)" -Level "INFO" -LogFile $logFilePath
Write-Log "Целевая файловая группа: $(if ([string]::IsNullOrWhiteSpace($Config.TargetFileGroup)) { 'ФГ по умолчанию для каждой БД' } else { $Config.TargetFileGroup })" -Level "INFO" -LogFile $logFilePath
Write-Log "Базы данных: $($Config.Databases -join ', ')" -Level "INFO" -LogFile $logFilePath
# Проверка и импорт модуля dbatools
if (-not (Get-Module -Name dbatools -ListAvailable)) {
Write-Log "Модуль dbatools не установлен. Установите командой: Install-Module dbatools -Scope CurrentUser -Force" -Level "ERROR" -LogFile $logFilePath
exit 1
}
Import-Module dbatools -ErrorAction Stop
Write-Log "✓ Модуль dbatools загружен" -Level "SUCCESS" -LogFile $logFilePath
# Подключение к серверу
try {
$server = Connect-DbaInstance -SqlInstance $Config.SqlInstance -ErrorAction Stop
Write-Log "✓ Подключено к: $($Config.SqlInstance)" -Level "SUCCESS" -LogFile $logFilePath
} catch {
Write-Log "✗ Не удалось подключиться: $_" -Level "ERROR" -LogFile $logFilePath
exit 1
}
# Фильтрация баз данных
$databasesToProcess = Get-DbaDatabase -SqlInstance $server -Status Normal |
Where-Object {
($Config.Databases.Count -eq 0 -or $_.Name -in $Config.Databases) -and
$_.Name -notin $Config.ExcludeDatabases -and
$_.Name -notin @("master", "model", "msdb", "tempdb")
}
if ($databasesToProcess.Count -eq 0) {
Write-Log "Не найдено подходящих баз данных для обработки" -Level "WARN" -LogFile $logFilePath
exit 0
}
Write-Log "Найдено баз данных для обработки: $($databasesToProcess.Count)" -Level "INFO" -LogFile $logFilePath
foreach ($db in $databasesToProcess) {
Write-Log " → $($db.Name)" -Level "INFO" -LogFile $logFilePath
}
# Определение целевой файловой группы для каждой БД
$targetFileGroups = @{}
foreach ($db in $databasesToProcess) {
if ([string]::IsNullOrWhiteSpace($Config.TargetFileGroup)) {
# Используем ФГ по умолчанию для каждой БД
$fgQuery = "SELECT name FROM sys.filegroups WHERE is_default = 1"
try {
$defaultFG = Invoke-DbaQuery -SqlInstance $server -Database $db.Name -Query $fgQuery -ErrorAction Stop | Select-Object -ExpandProperty name
$targetFileGroups[$db.Name] = $defaultFG
Write-Log "БД $($db.Name): используется ФГ по умолчанию: $defaultFG" -Level "INFO" -LogFile $logFilePath
} catch {
Write-Log "Не удалось определить ФГ по умолчанию для БД $($db.Name): $_" -Level "ERROR" -LogFile $logFilePath
exit 1
}
} else {
# Проверяем существование указанной ФГ
$fgExists = Invoke-DbaQuery -SqlInstance $server -Database $db.Name -Query "
SELECT COUNT(*) AS Cnt FROM sys.filegroups WHERE name = @fg
" -SqlParameter @{ fg = $Config.TargetFileGroup } -ErrorAction SilentlyContinue | Select-Object -ExpandProperty Cnt
if ($fgExists -eq 0) {
Write-Log "ФГ '$($Config.TargetFileGroup)' отсутствует в БД $($db.Name)" -Level "ERROR" -LogFile $logFilePath
exit 1
}
$targetFileGroups[$db.Name] = $Config.TargetFileGroup
Write-Log "БД $($db.Name): целевая ФГ: $($Config.TargetFileGroup)" -Level "INFO" -LogFile $logFilePath
}
}
# Очистка старых бэкапов
if ($Config.BackupIndexes) {
Write-Log "=== ОЧИСТКА СТАРЫХ БЭКАПОВ ===" -Level "INFO" -LogFile $logFilePath
Cleanup-OldBackups -BackupPath $Config.BackupPath -RetentionDays $Config.BackupRetentionDays -LogFile $logFilePath
}
# Проверка существования таблицы логирования
Write-Log "Проверка таблицы логирования..." -Level "INFO" -LogFile $logFilePath
$checkTableQuery = @"
IF EXISTS (
SELECT 1 FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = 'dbo' AND t.name = 'CheckProjectTables_log'
)
SELECT 1 AS ExistsFlag
ELSE
SELECT 0 AS ExistsFlag
"@
$logTableExists = $false
try {
$result = Invoke-DbaQuery -SqlInstance $server -Database $Config.LogDatabase -Query $checkTableQuery -ErrorAction Stop
$logTableExists = ($result.ExistsFlag -eq 1)
if ($logTableExists) {
Write-Log "✓ Таблица логирования найдена: $($Config.LogDatabase).$($Config.LogTable)" -Level "SUCCESS" -LogFile $logFilePath
} else {
Write-Log "Таблица логирования не найдена. Создаём..." -Level "WARN" -LogFile $logFilePath
$createLogTableQuery = @"
CREATE TABLE $($Config.LogTable) (
Id INT IDENTITY(1,1) PRIMARY KEY,
DatabaseName SYSNAME NOT NULL,
SchemaName SYSNAME NOT NULL,
TableName SYSNAME NOT NULL,
IndexName SYSNAME NULL,
Command NVARCHAR(MAX) NOT NULL,
CommandType VARCHAR(50) NOT NULL,
CommandSubType VARCHAR(20) NOT NULL,
StartTime DATETIME2 NOT NULL DEFAULT GETDATE(),
StopTime DATETIME2 NULL,
Result VARCHAR(20) NULL,
ErrorNumber INT NULL,
ErrorMessage NVARCHAR(MAX) NULL
)
"@
Invoke-DbaQuery -SqlInstance $server -Database $Config.LogDatabase -Query $createLogTableQuery -ErrorAction Stop
Write-Log "✓ Таблица логирования создана" -Level "SUCCESS" -LogFile $logFilePath
}
} catch {
Write-Log "Не удалось проверить/создать таблицу логирования: $_" -Level "WARN" -LogFile $logFilePath
$Config.LogDatabase = $null
}
# Сбор метаданных индексов (ТОЛЬКО те, которые не в целевой ФГ)
Write-Log "Сбор метаданных индексов..." -Level "INFO" -LogFile $logFilePath
$allIndexes = @{}
$indexesToRebuild = @{}
$indexesToBackup = @{}
foreach ($db in $databasesToProcess) {
Write-Log " → Сбор из БД: $($db.Name)" -Level "INFO" -LogFile $logFilePath
$targetFG = $targetFileGroups[$db.Name]
$query = @"
WITH IndexDetails AS (
SELECT
s.name AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
i.is_primary_key,
i.is_unique_constraint,
ds.name AS CurrentFileGroup,
i.fill_factor,
p.rows AS RowCounts,
i.index_id,
i.object_id,
CASE WHEN i.type = 1 THEN 1 ELSE 0 END AS IsClustered,
i.has_filter,
i.filter_definition,
i.is_unique,
-- Проверка наличия ограничений (полнотекстовый ключ, XML индекс, пространственный индекс)
CASE
WHEN EXISTS (
SELECT 1 FROM sys.fulltext_indexes fti
WHERE fti.object_id = i.object_id AND fti.unique_index_id = i.index_id
) THEN 1
WHEN EXISTS (
SELECT 1 FROM sys.xml_indexes xi
WHERE xi.object_id = i.object_id AND xi.index_id = i.index_id
) THEN 1
WHEN EXISTS (
SELECT 1 FROM sys.spatial_indexes spi
WHERE spi.object_id = i.object_id AND spi.index_id = i.index_id
) THEN 1
ELSE 0
END AS HasConstraints
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE i.index_id > 0
AND i.is_disabled = 0
AND t.is_ms_shipped = 0
AND t.name <> 'sysdiagrams'
AND (@RecreateAll = 1 OR ds.name <> @TargetFG)
AND (@NonClusteredOnly = 0 OR i.type_desc <> 'CLUSTERED')
$(
if ($Config.Tables.Count -gt 0) {
"AND t.name IN ($($Config.Tables.ForEach({ "'$_'" }) -join ','))"
}
)
),
IndexColumns AS (
SELECT
ic.object_id,
ic.index_id,
STUFF((
SELECT ', ' + QUOTENAME(c.name) +
CASE WHEN ic2.is_descending_key = 1 THEN ' DESC' ELSE '' END
FROM sys.index_columns ic2
INNER JOIN sys.columns c ON ic2.object_id = c.object_id AND ic2.column_id = c.column_id
WHERE ic2.object_id = ic.object_id AND ic2.index_id = ic.index_id AND ic2.is_included_column = 0
ORDER BY ic2.key_ordinal
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS KeyColumns,
STUFF((
SELECT ', ' + QUOTENAME(c.name)
FROM sys.index_columns ic2
INNER JOIN sys.columns c ON ic2.object_id = c.object_id AND ic2.column_id = c.column_id
WHERE ic2.object_id = ic.object_id AND ic2.index_id = ic.index_id AND ic2.is_included_column = 1
ORDER BY ic2.index_column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS IncludedColumns
FROM sys.index_columns ic
GROUP BY ic.object_id, ic.index_id
)
SELECT
DB_NAME() AS DatabaseName,
id.SchemaName,
id.TableName,
id.IndexName,
id.IndexType,
id.is_primary_key,
id.is_unique_constraint,
id.CurrentFileGroup,
id.fill_factor,
id.RowCounts,
id.IsClustered,
ic.KeyColumns,
ic.IncludedColumns,
id.has_filter,
id.filter_definition,
id.is_unique,
id.HasConstraints
FROM IndexDetails id
INNER JOIN IndexColumns ic ON id.object_id = ic.object_id AND id.index_id = ic.index_id
WHERE ic.KeyColumns IS NOT NULL
ORDER BY id.IsClustered DESC, id.SchemaName, id.TableName, id.IndexName
"@
try {
$results = Invoke-DbaQuery -SqlInstance $server -Database $db.Name -Query $query -SqlParameter @{
RecreateAll = [int]($Config.RecreateUnconditionally)
TargetFG = $targetFG
NonClusteredOnly = [int]($Config.NonClusteredOnly)
} -ErrorAction Stop
$dbIndexes = @()
foreach ($row in $results) {
$dbIndexes += [PSCustomObject]@{
DatabaseName = $db.Name
SchemaName = $row.SchemaName
TableName = $row.TableName
IndexName = $row.IndexName
IndexType = $row.IndexType
IsPrimaryKey = $row.is_primary_key
IsUniqueConstraint= $row.is_unique_constraint
IsUnique = $row.is_unique
CurrentFileGroup = $row.CurrentFileGroup
FillFactor = $row.fill_factor
RowCounts = $row.RowCounts
IsClustered = $row.IsClustered
KeyColumns = $row.KeyColumns
IncludedColumns = $row.IncludedColumns
HasFilter = $row.has_filter
FilterDefinition = $row.filter_definition
HasConstraints = $row.HasConstraints
}
}
$allIndexes[$db.Name] = $dbIndexes
$indexesToRebuild[$db.Name] = $dbIndexes
# Для бэкапа используем только индексы, которые будут пересоздаваться
$indexesToBackup[$db.Name] = $dbIndexes
Write-Log "Найдено индексов для пересоздания в БД $($db.Name): $($dbIndexes.Count)" -Level "INFO" -LogFile $logFilePath
} catch {
Write-Log "Ошибка при сборе индексов из $($db.Name): $_" -Level "ERROR" -LogFile $logFilePath
}
}
# Проверка общего количества индексов для пересоздания
$totalIndexesToRebuild = ($indexesToRebuild.Values | ForEach-Object { $_.Count } | Measure-Object -Sum).Sum
if ($totalIndexesToRebuild -eq 0) {
Write-Log "Не найдено индексов для пересоздания (все уже в правильной файловой группе)" -Level "WARN" -LogFile $logFilePath
Write-Log "Скрипт завершён - нет индексов для обработки" -Level "INFO" -LogFile $logFilePath
exit 0
}
Write-Log "✓ Всего индексов для пересоздания: $totalIndexesToRebuild" -Level "SUCCESS" -LogFile $logFilePath
# Создание резервной копии индексов (ТОЛЬКО для индексов, которые будут пересоздаваться)
if ($Config.BackupIndexes) {
Write-Log "=== СОЗДАНИЕ РЕЗЕРВНЫХ КОПИЙ ИНДЕКСОВ ===" -Level "INFO" -LogFile $logFilePath
$backupCount = Backup-Indexes -SqlInstance $Config.SqlInstance -IndexesToBackup $indexesToBackup -BackupPath $Config.BackupPath -LogFile $logFilePath -LogDatabase $Config.LogDatabase -LogTable $Config.LogTable
Write-Log "===========================================" -Level "INFO" -LogFile $logFilePath
}
# Генерация команд пересоздания
Write-Log "Генерация команд пересоздания..." -Level "INFO" -LogFile $logFilePath
$commands = foreach ($db in $databasesToProcess) {
$targetFG = $targetFileGroups[$db.Name]
$dbIndexes = $indexesToRebuild[$db.Name]
if (-not $dbIndexes) { continue }
foreach ($idx in $dbIndexes) {
# Формирование частей команды (БЕЗ пустых скобок INCLUDE)
$includePart = if (![string]::IsNullOrWhiteSpace($idx.IncludedColumns)) { " INCLUDE ($($idx.IncludedColumns))" } else { "" }
$filterPart = if ($idx.HasFilter -and $idx.FilterDefinition) { " WHERE $($idx.FilterDefinition)" } else { "" }
# FillFactor только если отличается от 80
$fillFactorOptions = @()
if ($idx.FillFactor -gt 0 -and $idx.FillFactor -ne 80) {
$fillFactorOptions += "FILLFACTOR = $($idx.FillFactor)"
}
# Для индексов с ограничениями пропускаем автоматическое пересоздание
if ($idx.HasConstraints -eq 1) {
$createCommand = "-- ИНДЕКС С ОГРАНИЧЕНИЯМИ (полнотекстовый ключ, XML или пространственный индекс)`n" +
"-- Требуется ручное пересоздание`n" +
"PRINT 'Индекс $($idx.IndexName) имеет ограничения. Пропускаем автоматическое пересоздание.';"
$cmdType = "SKIP (HAS CONSTRAINTS)"
$skipIndex = $true
} else {
# Для обычных индексов используем пересоздание через DROP_EXISTING
$fillFactorOptions += "DROP_EXISTING = ON"
$fillFactorOptions += "ONLINE = OFF"
$fillFactorOptions += "SORT_IN_TEMPDB = ON"
$fillFactorOptions += "MAXDOP = 4"
$withClause = " WITH (" + ($fillFactorOptions -join ", ") + ")"
$isUnique = if ($idx.IsUnique -or $idx.IsPrimaryKey -or $idx.IsUniqueConstraint) { "UNIQUE " } else { "" }
$createCommand = "CREATE ${isUnique}$($idx.IndexType) INDEX [$($idx.IndexName)] " +
"ON [$($idx.SchemaName)].[$($idx.TableName)]($($idx.KeyColumns))" +
$includePart + $filterPart + $withClause +
" ON [$targetFG];"
$cmdType = "CREATE INDEX"
$skipIndex = $false
}
[PSCustomObject]@{
DatabaseName = $idx.DatabaseName
SchemaName = $idx.SchemaName
TableName = $idx.TableName
IndexName = $idx.IndexName
CurrentFileGroup = $idx.CurrentFileGroup
TargetFileGroup = $targetFG
Command = $createCommand
CommandType = $cmdType
CommandSubType = if ($skipIndex) { "Skip" } else { "Create" }
EstimatedRows = $idx.RowCounts
IsClustered = $idx.IsClustered
HasConstraints = $idx.HasConstraints
SkipIndex = $skipIndex
}
}
}
Write-Log "✓ Сгенерировано команд: $($commands.Count)" -Level "SUCCESS" -LogFile $logFilePath
# Вывод плана выполнения
Write-Log "=== ПЛАН ВЫПОЛНЕНИЯ ===" -Level "INFO" -LogFile $logFilePath
Write-Log "Всего индексов для пересоздания: $($commands.Count)" -Level "INFO" -LogFile $logFilePath
Write-Log "Кластерных: $($commands.Where({ $_.IsClustered -eq $true }).Count)" -Level "INFO" -LogFile $logFilePath
Write-Log "Некластерных: $($commands.Where({ $_.IsClustered -eq $false }).Count)" -Level "INFO" -LogFile $logFilePath
Write-Log "С ограничениями (будут пропущены): $($commands.Where({ $_.HasConstraints -eq 1 }).Count)" -Level "INFO" -LogFile $logFilePath
if ($Config.PreviewOnly) {
Write-Log "[РЕЖИМ ПРЕДПРОСМОТРА] Команды НЕ будут выполнены" -Level "WARN" -LogFile $logFilePath
$outputFile = Join-Path -Path $Config.LogPath -ChildPath "IndexRebuildCommands_$(Get-Date -Format 'yyyyMMdd_HHmmss').sql"
$commands | ForEach-Object {
"USE [$($_.DatabaseName)];`n$($_.Command)`nGO`n"
} | Out-File -FilePath $outputFile -Encoding utf8
Write-Log "Команды сохранены в файл: $outputFile" -Level "SUCCESS" -LogFile $logFilePath
exit 0
}
# Подтверждение выполнения
Write-Host "`n" ("!" * 80) -ForegroundColor Red
Write-Host "ВНИМАНИЕ: Будут пересозданы $($commands.Count) индексов!" -ForegroundColor Red
Write-Host "Это может занять значительное время и вызвать блокировки." -ForegroundColor Red
Write-Host ("!" * 80) -ForegroundColor Red
Write-Host "`nПродолжить выполнение? (YES/NO)" -ForegroundColor Yellow -NoNewline
$confirmation = Read-Host
if ($confirmation -notlike "YES*") {
Write-Log "Операция отменена пользователем" -Level "WARN" -LogFile $logFilePath
exit 0
}
# Выполнение с логированием
Write-Log "=== НАЧАЛО ВЫПОЛНЕНИЯ ===" -Level "INFO" -LogFile $logFilePath
$total = $commands.Count
$current = 0
$stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
$successCount = 0
$failCount = 0
$skippedCount = 0
foreach ($cmd in ($commands | Sort-Object DatabaseName, IsClustered -Descending)) {
$current++
$progress = [math]::Round(($current / $total) * 100, 1)
Write-Progress -Activity "Пересоздание индексов" `
-Status "БД: $($cmd.DatabaseName) | Индекс: $($cmd.IndexName)" `
-PercentComplete $progress `
-CurrentOperation "[$current/$total] $progress%"
Write-Log "[$current/$total] Обработка: $($cmd.IndexName) (БД: $($cmd.DatabaseName))" -Level "INFO" -LogFile $logFilePath
# Логирование начала операции
$logId = $null
if ($Config.LogDatabase) {
try {
$logResult = Invoke-DbaQuery -SqlInstance $server -Database $Config.LogDatabase -Query @"
INSERT INTO $($Config.LogTable) (DatabaseName, SchemaName, TableName, IndexName, Command, CommandType, CommandSubType, StartTime)
VALUES (@db, @schema, @table, @index, @cmd, @cmdType, @cmdSubType, GETDATE());
SELECT CAST(SCOPE_IDENTITY() AS INT) AS Id;
"@ -SqlParameter @{
db = $cmd.DatabaseName
schema = $cmd.SchemaName
table = $cmd.TableName
index = $cmd.IndexName
cmd = $cmd.Command
cmdType = $cmd.CommandType
cmdSubType = $cmd.CommandSubType
} -ErrorAction Stop
$logId = $logResult.Id
} catch {
Write-Log "Не удалось записать в лог БД: $_" -Level "WARN" -LogFile $logFilePath
}
}
try {
# Пропускаем индексы с ограничениями
if ($cmd.SkipIndex -eq $true) {
$result = "Skipped"
$errorNum = 0
$errorMsg = "Индекс имеет ограничения (полнотекстовый ключ, XML или пространственный индекс)"
$skippedCount++
Write-Log " ⚠ $($cmd.IndexName) ($($cmd.DatabaseName)) - ПРОПУЩЕН (имеет ограничения)" -Level "WARN" -LogFile $logFilePath
} else {
# Выполнение команды
Invoke-DbaQuery -SqlInstance $server -Database $cmd.DatabaseName -Query $cmd.Command -QueryTimeout 3600 -ErrorAction Stop
$result = "Success"
$errorNum = $null
$errorMsg = $null
$successCount++
Write-Log " ✓ $($cmd.IndexName) ($($cmd.DatabaseName))" -Level "SUCCESS" -LogFile $logFilePath
}
} catch {
$result = "Fail"
$errorNum = if ($_.Exception.InnerException -and $_.Exception.InnerException.Number) {
$_.Exception.InnerException.Number
} else {
0
}
$errorMsg = $_.Exception.Message
$failCount++
Write-Log " ✗ $($cmd.IndexName) ($($cmd.DatabaseName)): $errorMsg" -Level "ERROR" -LogFile $logFilePath
}
# Логирование результата
if ($Config.LogDatabase -and $logId) {
try {
$null = Invoke-DbaQuery -SqlInstance $server -Database $Config.LogDatabase -Query @"
UPDATE $($Config.LogTable)
SET StopTime = GETDATE(), Result = @res, ErrorNumber = @errNum, ErrorMessage = @errMsg
WHERE Id = @id;
"@ -SqlParameter @{
res = $result
errNum = $errorNum
errMsg = $errorMsg
id = $logId
} -ErrorAction Stop
} catch {
Write-Log "Не удалось обновить лог БД: $_" -Level "WARN" -LogFile $logFilePath
}
}
}
$stopwatch.Stop()
Write-Progress -Activity "Завершено" -Completed
# Итоговая статистика
Write-Log "=== ИТОГИ ВЫПОЛНЕНИЯ ===" -Level "INFO" -LogFile $logFilePath
Write-Log "Всего операций: $total" -Level "INFO" -LogFile $logFilePath
Write-Log "Успешно: $successCount ($([math]::Round($successCount/$total*100, 2))%)" -Level "SUCCESS" -LogFile $logFilePath
Write-Log "Ошибок: $failCount" -Level $(if ($failCount -gt 0) { "ERROR" } else { "SUCCESS" }) -LogFile $logFilePath
Write-Log "Пропущено (с ограничениями): $skippedCount" -Level "WARN" -LogFile $logFilePath
Write-Log "Время выполнения: $($stopwatch.Elapsed.ToString('hh\:mm\:ss'))" -Level "INFO" -LogFile $logFilePath
Write-Host "`n" ("=" * 80) -ForegroundColor Green
Write-Host "ИТОГИ ВЫПОЛНЕНИЯ" -ForegroundColor Green
Write-Host ("=" * 80) -ForegroundColor Green
Write-Host "Всего операций: $total" -ForegroundColor Cyan
Write-Host "Успешно: $successCount ($([math]::Round($successCount/$total*100, 2))%)" -ForegroundColor ($successCount -eq $total ? "Green" : "Yellow")
Write-Host "Ошибок: $failCount" -ForegroundColor ($failCount -gt 0 ? "Red" : "Green")
Write-Host "Пропущено (с ограничениями): $skippedCount" -ForegroundColor Yellow
Write-Host "Время выполнения: $($stopwatch.Elapsed.ToString('hh\:mm\:ss'))" -ForegroundColor Cyan
Write-Host "Лог-файл: $logFilePath" -ForegroundColor Cyan
# Финальная проверка всех индексов
Write-Log "=== ФИНАЛЬНАЯ ПРОВЕРКА ИНДЕКСОВ ===" -Level "INFO" -LogFile $logFilePath
$indexesNotInTarget = Check-IndexesInFileGroup -SqlInstance $Config.SqlInstance -Databases $Config.Databases -TargetFileGroups $targetFileGroups -LogFile $logFilePath
if ($indexesNotInTarget.Count -gt 0) {
Write-Host "`n⚠ Найдены индексы, которые НЕ были перенесены в целевую файловую группу!" -ForegroundColor Yellow
Write-Host "Смотрите детали в лог-файле: $logFilePath" -ForegroundColor Yellow
} else {
Write-Host "`n✓ ВСЕ индексы успешно перенесены в целевую файловую группу!" -ForegroundColor Green
}
Write-Log "Скрипт завершён" -Level "INFO" -LogFile $logFilePath
Write-Host "`nСкрипт завершён. Подробности в лог-файле: $logFilePath" -ForegroundColor Green
Write-Host ("=" * 80) -ForegroundColor Green
Создать базу dbatools
USE [master]
GO
/****** Object: Database [dbatools] Script Date: 11.03.2026 13:55:17 ******/
CREATE DATABASE [dbatools]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'dbatools', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbatools.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'dbatools_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbatools_log.ldf' , SIZE = 73728KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [dbatools].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [dbatools] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [dbatools] SET ANSI_NULLS OFF
GO
ALTER DATABASE [dbatools] SET ANSI_PADDING OFF
GO
ALTER DATABASE [dbatools] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [dbatools] SET ARITHABORT OFF
GO
ALTER DATABASE [dbatools] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [dbatools] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [dbatools] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [dbatools] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [dbatools] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [dbatools] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [dbatools] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [dbatools] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [dbatools] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [dbatools] SET DISABLE_BROKER
GO
ALTER DATABASE [dbatools] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [dbatools] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [dbatools] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [dbatools] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [dbatools] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [dbatools] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [dbatools] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [dbatools] SET RECOVERY FULL
GO
ALTER DATABASE [dbatools] SET MULTI_USER
GO
ALTER DATABASE [dbatools] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [dbatools] SET DB_CHAINING OFF
GO
ALTER DATABASE [dbatools] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [dbatools] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [dbatools] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [dbatools] SET QUERY_STORE = OFF
GO
ALTER DATABASE [dbatools] SET READ_WRITE
GO
Создать таблицу
USE [dbatools]
GO
/****** Object: Table [dbo].[CheckProjectTables_log] Script Date: 11.03.2026 13:56:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CheckProjectTables_log](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[SchemaName] [sysname] NOT NULL,
[TableName] [sysname] NOT NULL,
[ColumnName] [sysname] NULL,
[IndexName] [sysname] NULL,
[Command] [varchar](max) NOT NULL,
[CommandType] [varchar](300) NOT NULL,
[CommandSubType] [varchar](300) NULL,
[StartTime] [datetime] NOT NULL,
[StopTime] [datetime] NULL,
[Result] [varchar](300) NULL,
[ErrorNumber] [int] NULL,
[ErrorMessage] [varchar](max) NULL,
CONSTRAINT [PK_CheckProjectTables_log] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Узнать в какой файловой группе индексы
DECLARE @Sql NVARCHAR(MAX)
DECLARE @DbName NVARCHAR(128)
-- Курсор для обхода баз данных
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE' -- Исключаем системные базы данных
-- Удаление временных таблиц, если они уже существуют
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL
BEGIN
DROP TABLE tempdb..#DatabaseFG;
END
IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL
BEGIN
DROP TABLE tempdb..#SysFiles;
END
-- Создание временных таблиц
CREATE TABLE #DatabaseFG (
DatabaseName NVARCHAR(128),
FG_name NVARCHAR(128),
data_space_id INT,
type NVARCHAR(128),
is_default INT,
is_autogrow_all_files INT
);
CREATE TABLE #SysFiles (
DatabaseName NVARCHAR(128),
FileName NVARCHAR(128),
FileSizeMB DECIMAL(18, 2),
SpaceUsedMB DECIMAL(18, 2),
FreeSpaceMB DECIMAL(18, 2)
);
-- Открытие курсора и обработка баз данных
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = 'USE [' + @DbName + ']; ' +
'INSERT INTO #DatabaseFG (DatabaseName, FG_name, data_space_id, type, is_default, is_autogrow_all_files) ' +
'SELECT ''' + @DbName + ''' as DatabaseName, name as FG_name, data_space_id, type, is_default, is_autogrow_all_files FROM sys.filegroups; ' +
'INSERT INTO #SysFiles (DatabaseName, FileName, FileSizeMB, SpaceUsedMB, FreeSpaceMB) ' +
'SELECT ''' + @DbName + ''' AS DatabaseName, a.name AS FileName, ' +
'CONVERT(DECIMAL(18, 2), ROUND(a.size / 128.000, 2)) AS FileSizeMB, ' +
'CONVERT(DECIMAL(18, 2), ROUND(FILEPROPERTY(a.name, ''SpaceUsed'') / 128.000, 2)) AS SpaceUsedMB, ' +
'CONVERT(DECIMAL(18, 2), ROUND((a.size - FILEPROPERTY(a.name, ''SpaceUsed'')) / 128.000, 2)) AS FreeSpaceMB ' +
'FROM dbo.sysfiles a;';
EXEC sp_executesql @Sql;
FETCH NEXT FROM db_cursor INTO @DbName
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- Итоговый запрос с заменой точек на запятые
SELECT DISTINCT
DBF.DatabaseName,
DBF.FG_name,
DBF.is_default,
DBF.is_autogrow_all_files,
MS.physical_name AS CurrentLocation,
SF.FileName,
-- Замена точек на запятые в числовых полях
REPLACE(CONVERT(NVARCHAR(20), SF.FileSizeMB), '.', ',') AS FileSizeMB,
REPLACE(CONVERT(NVARCHAR(20), SF.SpaceUsedMB), '.', ',') AS SpaceUsedMB,
REPLACE(CONVERT(NVARCHAR(20), SF.FreeSpaceMB), '.', ',') AS FreeSpaceMB,
REPLACE(CONVERT(NVARCHAR(20), CAST(SUM(MS.size) * 8. / 1024/1024 AS DECIMAL(18, 2))), '.', ',') AS total_size_gb,
REPLACE(CONVERT(NVARCHAR(20), CAST(SUM(MS.size) * 8. / 1024/1024/1024 AS DECIMAL(18, 2))), '.', ',') AS total_size_tb,
-- Скрытое поле для сортировки (включаем в SELECT для DISTINCT)
CAST(SUM(MS.size) * 8. / 1024/1024 AS DECIMAL(18, 2)) AS total_size_gb_numeric
FROM #DatabaseFG AS DBF
LEFT JOIN sys.master_files AS MS WITH (NOWAIT)
ON DBF.DatabaseName = DB_NAME(MS.database_id) AND DBF.data_space_id = MS.data_space_id
LEFT JOIN #SysFiles AS SF
ON DBF.DatabaseName = SF.DatabaseName AND SF.FileName = MS.name
--WHERE DBF.is_default = 1 and DBF.FG_name = 'PRIMARY'
GROUP BY
DBF.DatabaseName, DBF.FG_name, DBF.is_default, DBF.is_autogrow_all_files,
MS.physical_name, SF.FileName, SF.FileSizeMB, SF.SpaceUsedMB, SF.FreeSpaceMB
ORDER BY total_size_gb_numeric DESC;
-- Удаление временных таблиц
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL
BEGIN
DROP TABLE tempdb..#DatabaseFG;
END
IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL
BEGIN
DROP TABLE tempdb..#SysFiles;
END
Сами индексы в какой группе
--To determine what object is on which FG SELECT o.[name] AS TableName, i.[name] AS IndexName, fg.[name] AS FileGroupName FROM sys.indexes i INNER JOIN sys.filegroups fg ON i.data_space_id = fg.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE i.data_space_id = fg.data_space_id AND o.type = 'U' order by IndexName
Similar Posts:
- Как сбросить все сессии в статусе дисконект (disconnect) с фермы rds windows
- Как удалить у пользователя все группы кроме domain user
- Как завершить отключение сессии на серверах RDS и вывести список где пользователи не найдены на хостах но в брокере они есть.
- Как отключить не использующиеся индексы
- Как скопировать файл на компьютеры из группы