Практическая памятка для проверки занятого места, текущей нагрузки и истории роста файлов

1. Что важно понимать перед диагностикой

Свободное место Windows — место, которое видит операционная система на диске.

Свободное место внутри tempdb — уже зарезервированное SQL Server пространство внутри файлов tempdb. Windows и другие программы использовать его не могут.

Autogrowth (автоматический рост) — увеличение файла, когда текущего размера не хватает. После окончания операции файл сам обычно не уменьшается.

2. Рекомендуемый порядок действий при заполнении диска

1. Зафиксировать текущее время и сделать скриншот свободного места на диске.
2. Выполнить запрос № 1 — проверить свободное место на разделе из SQL Server.
3. Выполнить запрос № 2 — понять, чем заняты файлы данных tempdb.
4. Выполнить запрос № 3 — отдельно проверить журнал tempdb.
5. Выполнить запрос № 4 — посмотреть каждый файл и параметры автоприроста.
6. Если нагрузка ещё идёт — выполнить запрос № 5 и сохранить результат.
7. После завершения нагрузки — выполнить запросы № 6 и № 7, чтобы понять стартовый размер и историю роста.

3. Диагностические SQL-запросы

Запрос № 1. Свободное место на диске, где расположена tempdb

Показывает общий объём раздела, свободное место в гигабайтах и процентах. Это именно свободное место Windows, а не свободные страницы внутри tempdb.

SELECT DISTINCT
    vs.volume_mount_point AS disk,
    CAST(vs.total_bytes / 1073741824.0 AS decimal(18,2)) AS total_gb,
    CAST(vs.available_bytes / 1073741824.0 AS decimal(18,2)) AS free_gb,
    CAST(
        100.0 * vs.available_bytes / NULLIF(vs.total_bytes, 0)
        AS decimal(6,2)
    ) AS free_percent
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
WHERE mf.database_id = DB_ID(N'tempdb');

Как читать результат. Если free_gb близок к нулю, раздел заполнен. Даже при свободном месте внутри tempdb это требует реакции.

Запрос № 2. Общая занятость файлов данных tempdb

Главный диагностический запрос. Показывает общий размер файлов данных tempdb и распределение занятого пространства. Журнал templog здесь не учитывается.

USE tempdb;
GO

SELECT
    CAST(SUM(total_page_count) * 8.0 / 1024 AS decimal(18,1))
        AS total_data_mb,

    CAST(
        (SUM(total_page_count) - SUM(unallocated_extent_page_count))
        * 8.0 / 1024
        AS decimal(18,1)
    ) AS used_data_mb,

    CAST(SUM(unallocated_extent_page_count) * 8.0 / 1024
        AS decimal(18,1))
        AS free_data_mb,

    CAST(SUM(user_object_reserved_page_count) * 8.0 / 1024
        AS decimal(18,1))
        AS user_objects_mb,

    CAST(SUM(internal_object_reserved_page_count) * 8.0 / 1024
        AS decimal(18,1))
        AS internal_objects_mb,

    CAST(SUM(version_store_reserved_page_count) * 8.0 / 1024
        AS decimal(18,1))
        AS version_store_mb
FROM sys.dm_db_file_space_usage;

Как читать результат. user_objects_mb — временные таблицы; internal_objects_mb — сортировки, HASH JOIN, рабочие таблицы, некоторые операции с индексами; version_store_mb — версии строк; free_data_mb — свободно внутри файлов tempdb.

Запрос № 3. Использование журнала tempdb

Проверяет файл templog отдельно. Журнал может увеличиваться из-за большой или долго выполняющейся транзакции.

USE tempdb;
GO

SELECT
    CAST(total_log_size_in_bytes / 1048576.0 AS decimal(18,1))
        AS total_log_mb,
    CAST(used_log_space_in_bytes / 1048576.0 AS decimal(18,1))
        AS used_log_mb,
    CAST(used_log_space_in_percent AS decimal(6,2))
        AS used_log_percent
FROM sys.dm_db_log_space_usage;

Как читать результат. Если used_log_percent высокий и не снижается, нужно искать длинную транзакцию или активную тяжёлую операцию.

Запрос № 4. Текущие размеры всех файлов и автоприрост

Показывает каждый MDF/NDF/LDF-файл, его текущий размер, путь, шаг автоматического роста и максимальный размер. Для журнала занятость смотри запросом № 3.

USE tempdb;
GO

SELECT
    file_id,
    name AS file_name,
    type_desc,
    physical_name,

    CAST(size * 8.0 / 1024 AS decimal(18,1))
        AS current_size_mb,

    CASE
        WHEN type_desc = 'ROWS'
        THEN CAST(FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024
             AS decimal(18,1))
    END AS used_data_mb,

    CASE
        WHEN type_desc = 'ROWS'
        THEN CAST(
            (size - FILEPROPERTY(name, 'SpaceUsed')) * 8.0 / 1024
            AS decimal(18,1)
        )
    END AS free_data_mb,

    CASE
        WHEN is_percent_growth = 1
            THEN CAST(growth AS varchar(20)) + ' %'
        ELSE
            CAST(CAST(growth * 8.0 / 1024 AS decimal(18,1))
                AS varchar(30)) + ' MB'
    END AS autogrowth,

    CASE
        WHEN max_size = -1 THEN 'UNLIMITED'
        WHEN max_size = 0 THEN 'NO GROWTH'
        ELSE
            CAST(CAST(max_size * 8.0 / 1024 AS decimal(18,1))
                AS varchar(30)) + ' MB'
    END AS max_size
FROM sys.database_files
ORDER BY file_id;

Как читать результат. Файлы данных tempdb желательно держать одинакового размера и с одинаковым фиксированным шагом роста. UNLIMITED означает, что файл ограничен только свободным местом раздела.

Запрос № 5. Кто прямо сейчас использует tempdb

Запускать во время роста или тяжёлой операции. Показывает активные сеансы, компьютер, приложение, команду и примерный объём временного пространства.

USE tempdb;
GO

;WITH temp_usage AS
(
    SELECT
        session_id,
        request_id,
        SUM(
            user_objects_alloc_page_count
          - user_objects_dealloc_page_count
          + internal_objects_alloc_page_count
          - internal_objects_dealloc_page_count
        ) * 8.0 / 1024 AS tempdb_mb
    FROM sys.dm_db_task_space_usage
    GROUP BY session_id, request_id
)
SELECT TOP (30)
    CAST(u.tempdb_mb AS decimal(18,1)) AS tempdb_mb,
    u.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.wait_type,
    r.blocking_session_id,
    r.total_elapsed_time / 1000 AS elapsed_seconds,
    txt.text AS sql_text
FROM temp_usage AS u
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = u.session_id
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = u.session_id
   AND r.request_id = u.request_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    u.tempdb_mb > 0
    AND u.session_id <> @@SPID
ORDER BY u.tempdb_mb DESC;

Как читать результат. Если результат пустой, операция могла уже завершиться. Диагностика post factum (после события) часто не показывает виновника, поэтому этот запрос нужно запускать именно во время нагрузки.

Запрос № 6. Стартовый размер и рост после запуска SQL Server

Сравнивает размер tempdb при последнем запуске службы SQL Server с текущим размером.

SELECT
    mf.file_id,
    mf.name AS file_name,
    mf.physical_name,

    CAST(mf.size * 8.0 / 1024 AS decimal(18,1))
        AS startup_size_mb,

    CAST(df.size * 8.0 / 1024 AS decimal(18,1))
        AS current_size_mb,

    CAST(
        (df.size - mf.size) * 8.0 / 1024
        AS decimal(18,1)
    ) AS growth_since_start_mb
FROM sys.master_files AS mf
JOIN tempdb.sys.database_files AS df
    ON df.file_id = mf.file_id
WHERE mf.database_id = DB_ID(N'tempdb')
ORDER BY mf.file_id;

Как читать результат. Если growth_since_start_mb большой, файлы выросли уже после запуска. После планового перезапуска tempdb обычно пересоздаётся со стартовыми размерами, но перезапуск нельзя делать во время активной работы пользователей.

Запрос № 7. История автоматического роста tempdb

Читает стандартную трассировку SQL Server и показывает недавние события автоприроста файлов данных и журнала.

DECLARE @trace_path nvarchar(260);

SELECT @trace_path = path
FROM sys.traces
WHERE is_default = 1;

IF @trace_path IS NULL
BEGIN
    SELECT N'Default Trace отключён или недоступен' AS result;
END
ELSE
BEGIN
    SELECT TOP (100)
        StartTime,
        EndTime,
        CASE EventClass
            WHEN 92 THEN 'Data File Auto Grow'
            WHEN 93 THEN 'Log File Auto Grow'
        END AS event_name,
        DatabaseName,
        Filename,
        CAST(IntegerData * 8.0 / 1024 AS decimal(18,1))
            AS growth_mb,
        CAST(Duration / 1000000.0 AS decimal(18,3))
            AS duration_seconds,
        ApplicationName,
        HostName,
        LoginName
    FROM sys.fn_trace_gettable(@trace_path, DEFAULT)
    WHERE EventClass IN (92, 93)
      AND DatabaseName = N'tempdb'
    ORDER BY StartTime DESC;
END;

Как читать результат. История ограничена объёмом файлов трассировки и может уже не содержать старое событие. Пустой результат не доказывает, что роста не было.

Запрос № 8. Какая база занимает хранилище версий строк

Выполнять, если в запросе № 2 большое значение version_store_mb. Показывает, какая пользовательская база создаёт версии строк в tempdb.

SELECT
    DB_NAME(database_id) AS source_database,
    CAST(reserved_page_count * 8.0 / 1024 AS decimal(18,1))
        AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
WHERE reserved_page_count > 0
ORDER BY reserved_page_count DESC;

Как читать результат. Большой version store часто связан с длительными транзакциями или режимами изоляции на основе версий строк.

4. Быстрая расшифровка основных показателей

5. Что сохранить при повторении инцидента

• Точное время начала и окончания заполнения диска.
• Скриншот свободного места Windows.
• Результаты запросов № 1–5 во время нагрузки.
• Кто и какую операцию выполнял в 1С: закрытие месяца, перепроведение, отчёт, обмен, загрузка.
• Название компьютера и пользователя, если они видны в запросе № 5.
• Историю SQL Server Agent и события автоприроста по запросу № 7.

6. Чего не делать без отдельного плана

• Не удалять файлы tempdb.mdf, *.ndf и templog.ldf через Проводник.
• Не перезапускать SQL Server во время активной работы 1С и незавершённых операций.
• Не выполнять DBCC SHRINKFILE или DBCC SHRINKDATABASE только потому, что файл кажется большим.
• Не считать несколько мегабайт свободного места на разделе безопасным состоянием.
• Не ограничивать MAXSIZE случайным маленьким значением: следующая тяжёлая операция получит ошибку заполнения tempdb.

7. Короткая памятка по текущему случаю

В рассмотренном инциденте файлы tempdb резко заняли почти весь раздел во время тяжёлой операции 1С. После завершения операции большая часть пространства внутри tempdb освободилась, но физические файлы не уменьшились. Вероятная прикладная причина — закрытие периода и массовое перепроведение документов. Это не означает повреждение базы: SQL Server временно использовал tempdb как рабочее пространство.