Monitorowanie postępu BACKUP/RESTORE w SQL Server przy użyciu DMV

W środowiskach produkcyjnych backupy i restore’y często uruchamiane są z SQL Agent Job, przez inne osoby z SSMS lub przez automatyzacje. W takich sytuacjach DBA musi szybko odpowiedzieć na pytania:

• Jaki jest procent ukończenia backupu/restore?
• Ile czasu zostało do końca (ETA)?
• Czy operacja normalnie pracuje, czy czeka (I/O, blokady, CPU)?

SQL Server udostępnia do tego celu DMV (Dynamic Management Views). Najważniejszą w tym scenariuszu jest sys.dm_exec_requests, która pokazuje aktualnie wykonywane requesty, a dla wielu operacji administracyjnych również metryki postępu: percent_complete i estimated_completion_time.

BACKUP/RESTORE – procent ukończenia + ETA (minuty) + diagnostyka sesji

Poniższy skrypt zwraca wszystkie aktualnie wykonywane operacje BACKUP i RESTORE, wraz z informacjami „kto/skąd”, procentem postępu, estymowanym czasem zakończenia i waitami.

SELECT
    r.session_id                                   AS SPID,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id)                         AS database_name,
    r.command,
    r.start_time,
    r.percent_complete,
    r.status,
    r.wait_type,
    r.wait_time                                    AS wait_time_ms,
    r.blocking_session_id,
    r.estimated_completion_time                    AS est_ms,
    DATEADD(second, r.estimated_completion_time/1000.0, GETDATE()) AS estimated_finish_time,
    CAST(r.estimated_completion_time/1000.0/60.0 AS decimal(18,1))  AS est_minutes_remaining,
    a.text                                         AS sql_text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
    ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN
(
    'BACKUP DATABASE','BACKUP LOG',
    'RESTORE DATABASE','RESTORE LOG'
)
ORDER BY r.start_time DESC;

Jak czytać wynik

Identyfikacja procesu

• SPID (session_id) – sesja wykonująca operację.
• login_name / host_name / program_name – kto i skąd uruchomił (np. SQLAgent - TSQL JobStep, SSMS).

Postęp i czas

• percent_complete – procent ukończenia (dla BACKUP/RESTORE zazwyczaj działa, ale może długo być 0 na początku).
• estimated_completion_time (ms) – estymowany czas do końca; bywa 0 na starcie lub gdy silnik nie potrafi policzyć.
• est_minutes_remaining – przeliczenie na minuty (wygodne do komunikacji).
• estimated_finish_time – prognozowany czas zakończenia wg bieżącej estymacji.

Czy operacja „stoi”

• status – running lub suspended (suspended = czeka na zasób).
• wait_type / wait_time_ms – na co czeka (najczęściej I/O).
• blocking_session_id – czy jest blokowana.

Typowe pułapki

• Przy RESTORE DB_NAME(r.database_id) często pokazuje master, bo restore wykonuje się w kontekście master.
• sql_text (z dm_exec_sql_text) bywa niepełny albo nieprzydatny dla niektórych restore/backup — wtedy lepsze jest podejście z dm_exec_input_buffer (w dodatku).