На практике конфигурация tempdb почти на каждом сервере отличается: где-то 4 файла, где-то 8, размеры и MAXSIZE выставлены «исторически», кто-то когда-то добавил файл вручную и забыл про него. Единый скрипт ниже решает эту проблему: приводит количество файлов, их имена, размер и ограничения к одному стандарту сразу на всех серверах, при этом безопасно — с учётом того, что физическая структура tempdb пересоздаётся заново при каждом старте службы SQL Server.
Идея скрипта в двух словах
tempdb — единственная системная база, которая не подгружается с диска при старте службы, а создаётся заново по описанию в системном каталоге (sys.master_files). Это значит: изменить логическое описание файлов (имя, путь, размер, MAXSIZE) можно через ALTER DATABASE ... MODIFY FILE в любой момент, но чтобы изменения реально вступили в силу на диске — файлы физически пересоздались с новым именем/путём/размером — нужен рестарт службы.
Отсюда и деление на две фазы.
Фаза 1 — подготовка
Запускается первой, до рестарта. Делает следующее:
- убирает лишние файлы данных, если их больше, чем задано в
@TargetFileCount(черезDBCC SHRINKFILE ... EMPTYFILE, чтобы перенести данные из файла перед удалением, иALTER DATABASE ... REMOVE FILE); - переименовывает все оставшиеся файлы данных в единый вид
tempdevNNи перемещает их в указанный путь@DataPath(через промежуточные временные имена, чтобы избежать конфликтов при переименовании); - задаёт каждому файлу данных начальный размер
SIZE(рассчитанный как процент от будущего MAXSIZE,@InitialFillPercent) и шаг ростаFILEGROWTH; - на этом этапе MAXSIZE файлов данных всегда ставится в
UNLIMITED— временно, независимо от того, что вы в итоге хотите. Это не опечатка: пока физический файл ещё большой (от старой конфигурации), SQL Server не даст применитьMODIFY FILEс новым именем и меньшим размером, если MAXSIZE уже ограничен снизу текущего размера. UNLIMITED снимает это ограничение на переходный период; - настраивает файл лога templog: размер, MAXSIZE и прирост — сразу в финальном виде, без промежуточного шага, потому что лог не участвует в логике «сначала сожми, потом ограничь».
После этой фазы обязателен рестарт службы SQL Server — только тогда файлы данных реально пересоздадутся на диске с новыми именами, путями и уменьшенным размером.
Фаза 2 — финализация
Запускается после рестарта. К этому моменту файлы данных уже маленькие (созданы заново по параметрам из Фазы 1), и можно безопасно поставить итоговый MAXSIZE — либо рассчитанный автоматически по актуальному свободному месту на диске, либо фиксированный, либо снять ограничение вовсе.
Именно вынесение этого шага в отдельную фазу после рестарта даёт корректный расчёт: свободное место на диске оценивается уже «по факту», когда старые большие файлы удалены и заменены новыми маленькими, а не до этого, когда диск ещё занят прежними файлами.
Как выбрать: ограничение или UNLIMITED
Изначальная версия скрипта всегда считала MAXSIZE автоматически, по формуле «взять свободное место на диске, оставить резерв @ReservePercent, поделить на количество файлов». Это разумный дефолт, но не универсальный: иногда нужно явно ограничить рост меньшим значением (например, чтобы не дать tempdb занять весь диск при аварийном плане), а иногда — наоборот, снять ограничение полностью и полагаться только на мониторинг места на диске.
Поэтому в параметры добавлены два независимых переключателя.
Для файлов данных — @DataMaxSizeMode:
| Значение | Что делает |
|---|---|
AUTO |
MAXSIZE считается автоматически по свободному месту на диске (поведение по умолчанию, как раньше) |
UNLIMITED |
MAXSIZE снимается, файлы могут расти до заполнения диска |
FIXED |
MAXSIZE берётся из @DataMaxSizeFixedMB, которое задаёте сами |
Для лога — @LogMaxSizeMode:
| Значение | Что делает |
|---|---|
FIXED |
MAXSIZE = @LogFileMaxMB |
UNLIMITED |
MAXSIZE снимается |
Оба переключателя влияют только на то, какое значение подставляется в MAXSIZE соответствующего ALTER DATABASE ... MODIFY FILE; вся остальная логика скрипта (удаление лишних файлов, переименование, расчёт начального размера) не меняется.
Порядок запуска на каждом сервере
- Открыть параметры в начале скрипта, задать
@TargetFileCount, пути, режимы@DataMaxSizeMode/@LogMaxSizeMode(и@DataMaxSizeFixedMB, если выбранFIXED). - Установить
@Phase = 1, выполнить скрипт, проверить выводPRINTи итоговую выборку изsys.master_files. - Перезапустить службу SQL Server на этом сервере.
- Установить
@Phase = 2, выполнить тот же скрипт с теми же параметрами. - Проверить итоговую конфигурацию по выборке в конце скрипта: количество файлов, размеры, MAXSIZE у каждого.
Повторить шаги 1–5 одинаково на всех серверах — так конфигурация tempdb становится предсказуемой и одинаковой по всей инфраструктуре.
/* =========================================================================
ЕДИНЫЙ СКРИПТ СТАНДАРТИЗАЦИИ TEMPDB
Запускать ОДИНАКОВО на всех серверах: сначала @Phase = 1,
потом РЕСТАРТ службы SQL Server, потом этот же скрипт с @Phase = 2
ЧТО ДЕЛАЕТ КАЖДАЯ ФАЗА:
@Phase = 1
- Удаляет лишние файлы данных, если их больше, чем @TargetFileCount
(через DBCC SHRINKFILE ... EMPTYFILE + ALTER DATABASE ... REMOVE FILE).
- Переименовывает существующие файлы данных сначала во временные
имена (zzmig_NNN), затем в финальные (tempdevNN), и физически
перемещает их в @DataPath.
- Пересоздаёт размер каждого файла данных: SIZE = рассчитанный
@PerFileInitialMB, MAXSIZE = UNLIMITED (временно, это обязательно -
иначе SQL Server не даст корректно применить NEWNAME/FILENAME,
пока файлы физически большие), FILEGROWTH = @DataFileGrowthMB.
- Настраивает файл лога (templog): переименование при необходимости,
SIZE/MAXSIZE/FILEGROWTH по параметрам (MAXSIZE лога зависит от
@LogMaxSizeMode - см. ниже).
- Это ПОДГОТОВИТЕЛЬНАЯ фаза: физическое изменение файлов tempdb
(сжатие/переименование/перемещение) вступает в силу только после
РЕСТАРТА службы SQL Server, потому что tempdb пересоздаётся с нуля
при каждом старте службы.
@Phase = 2
- Выполняется ПОСЛЕ рестарта службы, когда файлы уже созданы заново
по параметрам, заданным в Фазе 1 (маленького начального размера).
- Единственное действие: проставляет финальный MAXSIZE на каждый
файл данных (UNLIMITED или фиксированное значение в MB - см.
@DataMaxSizeMode), рассчитанный по АКТУАЛЬНОМУ свободному месту
на диске на момент запуска Фазы 2.
- Отдельная фаза нужна потому, что сразу выставлять итоговый MAXSIZE
в Фазе 1 небезопасно: пока файлы физически большие, а после
рестарта они станут маленькими, и правильный расчёт запаса места
имеет смысл делать по состоянию диска "после", а не "до".
===================== БЛОК ПАРАМЕТРОВ (меняете только тут) =====================
*/
SET NOCOUNT ON;
DECLARE @Phase INT = 1; -- 1 = переименование/пересборка файлов, 2 = финальный MAXSIZE
DECLARE @TargetFileCount INT = 16; -- сколько файлов данных должно быть
DECLARE @DataPath NVARCHAR(260) = N'E:\tempdb\'; -- куда класть файлы данных
DECLARE @LogPath NVARCHAR(260) = N'E:\tempdb\'; -- куда класть лог
DECLARE @ReservePercent DECIMAL(5,2) = 20.0; -- % свободного места на диске оставить нетронутым (буфер)
DECLARE @InitialFillPercent DECIMAL(5,2) = 30.0; -- начальный SIZE = этот % от MAXSIZE на файл
DECLARE @DataFileGrowthMB INT = 1024; -- фиксированный прирост файла данных, MB
-- --------- НОВОЕ: режим MAXSIZE для файлов ДАННЫХ (используется в Фазе 2) ---------
-- 'AUTO' - рассчитать MAXSIZE автоматически по свободному месту на диске (как раньше)
-- 'UNLIMITED' - MAXSIZE = UNLIMITED, без ограничения
-- 'FIXED' - MAXSIZE = @DataMaxSizeFixedMB (фиксированное значение, задаёте сами)
DECLARE @DataMaxSizeMode NVARCHAR(20) = N'UNLIMITED';
DECLARE @DataMaxSizeFixedMB INT = NULL; -- обязателен, если @DataMaxSizeMode = 'FIXED'
DECLARE @LogFileSizeMB INT = 51200; -- размер лога, MB
DECLARE @LogFileMaxMB INT = 512000; -- maxsize лога, MB (используется, если @LogMaxSizeMode = 'FIXED')
DECLARE @LogFileGrowthMB INT = 2048; -- прирост лога, MB
-- --------- НОВОЕ: режим MAXSIZE для ЛОГА (используется в Фазе 1, где настраивается лог) ---------
-- 'FIXED' - MAXSIZE = @LogFileMaxMB
-- 'UNLIMITED' - MAXSIZE = UNLIMITED, без ограничения
DECLARE @LogMaxSizeMode NVARCHAR(20) = N'UNLIMITED';
/* ================================================================================ */
PRINT '=== Сервер: ' + @@SERVERNAME + ' | Фаза: ' + CAST(@Phase AS VARCHAR) + ' ===';
/* -------------------------------------------------------------------------
Автоматический расчёт размера файла данных по свободному месту на диске,
где физически лежит tempdb (определяем диск по текущему файлу tempdev)
------------------------------------------------------------------------- */
DECLARE @FreeBytes BIGINT;
SELECT TOP 1 @FreeBytes = vs.available_bytes
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
WHERE mf.database_id = DB_ID('tempdb') AND mf.type = 0;
IF @FreeBytes IS NULL
BEGIN
PRINT '!!! Не удалось определить свободное место на диске. Проверьте права/путь. Прерываю.';
RETURN;
END
DECLARE @BudgetBytes BIGINT = CAST(@FreeBytes * (1 - @ReservePercent / 100.0) AS BIGINT);
DECLARE @PerFileMaxMB INT = CAST(@BudgetBytes / @TargetFileCount / 1024 / 1024 AS INT);
DECLARE @PerFileInitialMB INT = CAST(@PerFileMaxMB * (@InitialFillPercent / 100.0) AS INT);
PRINT 'Свободно на диске сейчас: ' + CAST(CAST(@FreeBytes / 1024.0 / 1024 / 1024 AS DECIMAL(10,2)) AS VARCHAR) + ' GB';
PRINT 'Бюджет под tempdb (с запасом ' + CAST(@ReservePercent AS VARCHAR) + '%): '
+ CAST(CAST(@BudgetBytes / 1024.0 / 1024 / 1024 AS DECIMAL(10,2)) AS VARCHAR) + ' GB';
PRINT 'Авторасчёт MAXSIZE на файл (если @DataMaxSizeMode = AUTO): ' + CAST(@PerFileMaxMB AS VARCHAR) + ' MB | Начальный SIZE на файл: ' + CAST(@PerFileInitialMB AS VARCHAR) + ' MB';
PRINT '---------------------------------------------------------';
-- --------- НОВОЕ: собираем итоговую строку MAXSIZE для файлов данных (Фаза 2) ---------
IF @DataMaxSizeMode = N'FIXED' AND @DataMaxSizeFixedMB IS NULL
BEGIN
PRINT '!!! @DataMaxSizeMode = FIXED, но @DataMaxSizeFixedMB не задан. Прерываю.';
RETURN;
END
DECLARE @DataMaxSizeClause NVARCHAR(20);
IF @DataMaxSizeMode = N'UNLIMITED'
SET @DataMaxSizeClause = N'UNLIMITED';
ELSE IF @DataMaxSizeMode = N'FIXED'
SET @DataMaxSizeClause = CAST(@DataMaxSizeFixedMB AS NVARCHAR(20)) + N'MB';
ELSE -- AUTO
SET @DataMaxSizeClause = CAST(@PerFileMaxMB AS NVARCHAR(20)) + N'MB';
PRINT 'Итоговый MAXSIZE для файлов данных (режим ' + @DataMaxSizeMode + '): ' + @DataMaxSizeClause;
-- --------- НОВОЕ: собираем итоговую строку MAXSIZE для лога (Фаза 1) ---------
DECLARE @LogMaxSizeClause NVARCHAR(20);
IF @LogMaxSizeMode = N'UNLIMITED'
SET @LogMaxSizeClause = N'UNLIMITED';
ELSE
SET @LogMaxSizeClause = CAST(@LogFileMaxMB AS NVARCHAR(20)) + N'MB';
PRINT 'Итоговый MAXSIZE для лога (режим ' + @LogMaxSizeMode + '): ' + @LogMaxSizeClause;
PRINT '---------------------------------------------------------';
/* ===========================================================================
ФАЗА 1: чистим лишние файлы, приводим имена/размер к единому виду.
MAXSIZE файлов ДАННЫХ временно всегда = UNLIMITED (нельзя сразу
ограничить, пока файлы физически большие) - это техническая
необходимость и не зависит от @DataMaxSizeMode.
MAXSIZE ЛОГА выставляется сразу по @LogMaxSizeMode.
=========================================================================== */
IF @Phase = 1
BEGIN
-- ШАГ 0. Убираем лишние файлы данных, если их больше нужного
DECLARE @cnt INT, @excess INT;
SELECT @cnt = COUNT(*) FROM sys.master_files WHERE database_id = DB_ID('tempdb') AND type = 0;
SET @excess = @cnt - @TargetFileCount;
IF @excess > 0
BEGIN
PRINT 'Обнаружено ' + CAST(@excess AS VARCHAR) + ' лишних файлов данных. Удаляю файлы с наибольшим file_id...';
DECLARE @exname NVARCHAR(128), @exsql NVARCHAR(MAX);
DECLARE excess_cursor CURSOR FAST_FORWARD FOR
SELECT TOP (@excess) name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb') AND type = 0
ORDER BY file_id DESC;
OPEN excess_cursor;
FETCH NEXT FROM excess_cursor INTO @exname;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @exsql = N'DBCC SHRINKFILE(N''' + @exname + N''', EMPTYFILE);';
PRINT @exsql; EXEC sp_executesql @exsql;
SET @exsql = N'ALTER DATABASE tempdb REMOVE FILE ' + QUOTENAME(@exname) + N';';
PRINT @exsql; EXEC sp_executesql @exsql;
END TRY
BEGIN CATCH
PRINT '!!! Не удалось удалить ' + @exname + ': ' + ERROR_MESSAGE() + ' — уберите вручную.';
END CATCH
FETCH NEXT FROM excess_cursor INTO @exname;
END
CLOSE excess_cursor; DEALLOCATE excess_cursor;
END
ELSE IF @excess < 0
PRINT 'Файлов данных ' + CAST(@cnt AS VARCHAR) + ', меньше целевых ' + CAST(@TargetFileCount AS VARCHAR)
+ '. Недостающие нужно будет добавить ADD FILE отдельно (за пределами этого скрипта).';
-- ШАГ 1. Переименование в уникальные временные имена + resize (MAXSIZE = UNLIMITED)
DECLARE @name NVARCHAR(128), @rownum INT, @sql NVARCHAR(MAX);
DECLARE @tmpname NVARCHAR(128), @tmppath NVARCHAR(260);
DECLARE mig_cursor CURSOR FAST_FORWARD FOR
SELECT name, ROW_NUMBER() OVER (ORDER BY file_id)
FROM sys.master_files
WHERE database_id = DB_ID('tempdb') AND type = 0
ORDER BY file_id;
OPEN mig_cursor;
FETCH NEXT FROM mig_cursor INTO @name, @rownum;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tmpname = N'zzmig_' + RIGHT('0' + CAST(@rownum AS VARCHAR(3)), 3);
SET @tmppath = @DataPath + @tmpname + N'.ndf';
SET @sql = N'ALTER DATABASE tempdb MODIFY FILE (NAME = N''' + @name + N''',
NEWNAME = N''' + @tmpname + N''',
FILENAME = N''' + @tmppath + N''',
SIZE = ' + CAST(@PerFileInitialMB AS NVARCHAR(20)) + N'MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = ' + CAST(@DataFileGrowthMB AS NVARCHAR(20)) + N'MB);';
PRINT @sql;
EXEC sp_executesql @sql;
FETCH NEXT FROM mig_cursor INTO @name, @rownum;
END
CLOSE mig_cursor; DEALLOCATE mig_cursor;
-- ШАГ 2. Переименование из временных имён в финальные tempdevNN
DECLARE @finalname NVARCHAR(128), @finalpath NVARCHAR(260);
DECLARE fin_cursor CURSOR FAST_FORWARD FOR
SELECT name, ROW_NUMBER() OVER (ORDER BY file_id)
FROM sys.master_files
WHERE database_id = DB_ID('tempdb') AND type = 0
ORDER BY file_id;
OPEN fin_cursor;
FETCH NEXT FROM fin_cursor INTO @name, @rownum;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @finalname = N'tempdev' + RIGHT('0' + CAST(@rownum AS VARCHAR(3)), 2);
SET @finalpath = @DataPath + @finalname + N'.ndf';
IF @name <> @finalname
BEGIN
SET @sql = N'ALTER DATABASE tempdb MODIFY FILE (NAME = N''' + @name + N''',
NEWNAME = N''' + @finalname + N''',
FILENAME = N''' + @finalpath + N''');';
PRINT @sql;
EXEC sp_executesql @sql;
END
ELSE
PRINT 'Пропуск: ' + @name + ' уже имеет финальное имя.';
FETCH NEXT FROM fin_cursor INTO @name, @rownum;
END
CLOSE fin_cursor; DEALLOCATE fin_cursor;
-- ШАГ 3. Лог (MAXSIZE берём из @LogMaxSizeClause: FIXED или UNLIMITED)
DECLARE @logname NVARCHAR(128);
SELECT @logname = name FROM sys.master_files WHERE database_id = DB_ID('tempdb') AND type = 1;
DECLARE @LogFilePath NVARCHAR(260) = @LogPath + N'templog.ldf';
DECLARE @logsql NVARCHAR(MAX);
IF @logname <> N'templog'
SET @logsql = N'ALTER DATABASE tempdb MODIFY FILE (NAME = N''' + @logname + N''',
NEWNAME = N''templog'',
FILENAME = N''' + @LogFilePath + N''',
SIZE = ' + CAST(@LogFileSizeMB AS NVARCHAR(20)) + N'MB,
MAXSIZE = ' + @LogMaxSizeClause + N',
FILEGROWTH = ' + CAST(@LogFileGrowthMB AS NVARCHAR(20)) + N'MB);';
ELSE
SET @logsql = N'ALTER DATABASE tempdb MODIFY FILE (NAME = N''templog'',
SIZE = ' + CAST(@LogFileSizeMB AS NVARCHAR(20)) + N'MB,
MAXSIZE = ' + @LogMaxSizeClause + N',
FILEGROWTH = ' + CAST(@LogFileGrowthMB AS NVARCHAR(20)) + N'MB);';
PRINT @logsql;
EXEC sp_executesql @logsql;
PRINT '=== ФАЗА 1 завершена. SIZE=' + CAST(@PerFileInitialMB AS VARCHAR) + 'MB, MAXSIZE(данные)=UNLIMITED (временно). Далее: РЕСТАРТ службы SQL Server, потом этот же скрипт с @Phase = 2. ===';
END
/* ===========================================================================
ФАЗА 2: после рестарта файлы уже маленькие — можно смело поставить
финальный MAXSIZE, взятый из @DataMaxSizeClause (AUTO/UNLIMITED/FIXED)
=========================================================================== */
IF @Phase = 2
BEGIN
DECLARE @name2 NVARCHAR(128), @sql2 NVARCHAR(MAX);
DECLARE fin2_cursor CURSOR FAST_FORWARD FOR
SELECT name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb') AND type = 0
ORDER BY file_id;
OPEN fin2_cursor;
FETCH NEXT FROM fin2_cursor INTO @name2;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql2 = N'ALTER DATABASE tempdb MODIFY FILE (NAME = N''' + @name2 + N''', MAXSIZE = '
+ @DataMaxSizeClause + N');';
PRINT @sql2;
EXEC sp_executesql @sql2;
FETCH NEXT FROM fin2_cursor INTO @name2;
END
CLOSE fin2_cursor; DEALLOCATE fin2_cursor;
PRINT '=== ФАЗА 2 завершена. MAXSIZE(данные) = ' + @DataMaxSizeClause + ' на каждый файл (режим ' + @DataMaxSizeMode + '). ===';
END
/* ---------------------------------- Итоговая проверка ---------------------------------- */
SELECT @@SERVERNAME AS server_name, file_id, name, physical_name, type_desc,
CAST(size AS BIGINT) * 8 / 1024 AS size_MB,
CASE WHEN max_size = -1 THEN NULL ELSE CAST(max_size AS BIGINT) * 8 / 1024 END AS maxsize_MB,
growth AS growth_MB
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
ORDER BY type, file_id;