Monitorowanie postępu BACKUP/RESTORE – praktyczny monitoring „jak w produkcji”

Ten dodatek dotyczy realnego problemu z produkcji: RESTORE uruchamiany z master, a także sytuacji, w której dm_exec_sql_text nie daje użytecznego tekstu. Wtedy najpewniejszym źródłem „co faktycznie uruchomiono” jest sys.dm_exec_input_buffer().

Wymagane uprawnienie: VIEW SERVER STATE.

1) Najbardziej niezawodny podgląd: użyj sys.dm_exec_input_buffer

Ten skrypt pokazuje wszystkie aktywne BACKUP/RESTORE, ale zamiast sql_text bierze polecenie z input buffer sesji.

SELECT
    r.session_id AS SPID,
    s.login_name,
    s.host_name,
    s.program_name,
    r.command,
    r.start_time,
    r.status,
    r.percent_complete,
    CAST(r.estimated_completion_time/1000.0/60.0 AS decimal(18,1)) AS est_minutes_remaining,
    DATEADD(second, r.estimated_completion_time/1000.0, GETDATE())  AS estimated_finish_time,
    r.wait_type,
    r.wait_time AS wait_time_ms,
    r.blocking_session_id,
    ib.event_info AS input_buffer_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_input_buffer(r.session_id, r.request_id) ib
WHERE r.command IN ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE','BACKUP LOG')
ORDER BY r.start_time DESC;

Dlaczego to jest lepsze w praktyce?

• Przy restore/backup sql_handle może być NULL lub wskazywać wewnętrzny request, przez co dm_exec_sql_text nie pomaga.
• dm_exec_input_buffer pokazuje polecenie, które klient/Agent wysłał do sesji (najbardziej „ludzkie” źródło prawdy).

2) Minimalna wersja do „Teams” (status w 5 sekund)

Jeśli potrzebujesz tylko „ile % i kiedy koniec”, bez kontekstu i bez tekstu polecenia:

SELECT
    r.session_id AS SPID,
    r.command,
    r.percent_complete,
    CAST(r.estimated_completion_time/1000.0/60.0 AS decimal(18,1)) AS est_minutes_remaining,
    DATEADD(second, r.estimated_completion_time/1000.0, GETDATE())  AS estimated_finish_time,
    r.status,
    r.wait_type
FROM sys.dm_exec_requests r
WHERE r.command IN ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE','BACKUP LOG')
ORDER BY r.start_time DESC;

To jest wersja, którą realnie odpalasz w stresie, żeby od razu wkleić do Teams:

• percent_complete + est_minutes_remaining + estimated_finish_time
• plus status i wait_type (żeby od razu wiedzieć, czy to I/O czy blokada)

3) Szybka ściąga: interpretacja wait_type podczas BACKUP/RESTORE

Gdy percent_complete nie rośnie, patrz na status i wait_type.

Najczęstsze (I/O)

• BACKUPIO – typowe dla backupu; zwykle ograniczenie po stronie storage/ścieżki docelowej.
• ASYNC_IO_COMPLETION / IO_COMPLETION – klasyczne czekanie na I/O; często storage/latencje/konkurencja o dysk.

W praktyce: jeżeli wait_time_ms rośnie i dominuje I/O, to wąskie gardło jest poza SQL (storage, sieć do udziału, wirtualizacja, throttling).

CPU / presja zasobów

• SOS_SCHEDULER_YIELD – presja CPU; ETA może „pływać”.
• THREADPOOL – brak workerów (problem całej instancji).

Blokady

• LCK_M_* – czeka na lock; sprawdzaj blocking_session_id.