Dlaczego tempdb nie chce się zmniejszyć?

W idealnym świecie SQL Server powinien sprawnie zarządzać przestrzenią. Jednak tempdb jest specyficzną bazą – to „brudnopis” całego serwera. Przechowuje ona obiekty tymczasowe, zmienne tabelaryczne, kursory oraz wersje wierszy (Row Versioning).

Głównym powodem, dla którego SHRINKFILE zawodzi, jest fakt, że SQL Server nie może przenieść stron danych, które są obecnie w użyciu lub są zablokowane w pamięci podręcznej.

1. Mechanizm blokowania wolnego miejsca

Podczas operacji shrinkowania, silnik SQL stara się przenieść strony z końca pliku na jego początek, aby móc odciąć pustą końcówkę i zwrócić ją do systemu operacyjnego. Jeśli na samym końcu pliku znajduje się choćby jedna „przypięta” strona, proces shrinkowania zatrzyma się tuż przed nią.

Przedstawione poniżej komendy czyszczą różne warstwy pamięci, które trzymają referencje do stron w tempdb.

DBCC FREEPROCCACHE

Ta komenda usuwa wszystkie plany zapytań z pamięci podręcznej (Plan Cache).

• Związek z tempdb: Niektóre plany zapytań generują tymczasowe tabele robocze (work tables) lub obiekty, które pozostają „powiązane” z sesją, dopóki plan jest aktywny w pamięci. Usunięcie planów wymusza ich ponowną kompilację i często zwalnia powiązane z nimi struktury w tempdb.

DBCC DROPCLEANBUFFERS

Usuwa wszystkie czyste strony (niezmodyfikowane dane) z Buffer Poola.

• Związek z tempdb: Chociaż głównie dotyczy to baz użytkownika, wymusza to na SQL Serverze odświeżenie stron w pamięci RAM. W połączeniu z innymi komendami pomaga „odkleić” system od aktualnie trzymanych w pamięci struktur danych.

DBCC FREESYSTEMCACHE (’ALL’)

To „najcięższe” działo. Czyści wszystkie cache systemowe, w tym meta dane , uprawnienia i – co najważniejsze – Temporary Object Cache.

• Związek z tempdb: SQL Server często cache’uje definicje tabel tymczasowych, aby szybciej ich używać przy kolejnych wywołaniach tych samych procedur. Ta komenda usuwa te definicje, co jest kluczowe dla powodzenia shrinkowania.

DBCC FREESESSIONCACHE

Usuwa pamięć podręczną rozproszonych zapytań (Distributed Queries) używaną dla połączeń między serwerami.

• Związek z tempdb: Pomaga pozbyć się pozostałości po sesjach, które mogły tworzyć obiekty tymczasowe w ramach zapytań do zdalnych źródeł danych.

2. Diagnostyka przed walką (Skrypt Raportujący)

Zanim zaczniesz czyścić cache, sprawdź, kto jest winowajcą.

SET NOCOUNT ON;

PRINT '--- 1. PLIKI (Czy jest wolne miejsce?) ---';
SELECT name, size*8/1024 AS SizeMB, 
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)*8/1024 AS UsedMB
FROM tempdb.sys.database_files;

PRINT '--- 2. ZUŻYCIE GLOBALNE (Z innego widoku) ---';
-- Ten widok pokazuje ogólny stan tempdb bez rozbicia na sesje
SELECT 
    user_object_reserved_page_count * 8 / 1024 AS UserObjMB,
    internal_object_reserved_page_count * 8 / 1024 AS InternalObjMB,
    version_store_reserved_page_count * 8 / 1024 AS VersionStoreMB,
    unallocated_extent_page_count * 8 / 1024 AS FreeSpaceMB
FROM tempdb.sys.dm_db_file_space_usage;

PRINT '--- 3. AKTYWNE ZADANIA (Kto teraz obciąża tempdb?) ---';
-- Sprawdzamy zadania (tasks), bo to one najczęściej blokują plik
SELECT TOP 5
    session_id,
    request_id,
    user_objects_alloc_page_count * 8 / 1024 AS UserMB,
    internal_objects_alloc_page_count * 8 / 1024 AS InternalMB
FROM tempdb.sys.dm_db_task_space_usage
WHERE (user_objects_alloc_page_count + internal_objects_alloc_page_count) > 0
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC;

PRINT '--- 4. NAJSTARSZA TRANSAKCJA ---';
SELECT TOP 5
    st.session_id,
    at.transaction_begin_time,
    DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS DurationMin
FROM sys.dm_tran_active_transactions at
JOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id
ORDER BY at.transaction_begin_time ASC;

3. Procedura „Złotego Standardu” (Krok po kroku)

Jeśli diagnostyka wykazała, że masz wolne miejsce (FreeSpaceMB jest wysokie), ale plik fizyczny nie maleje, postępuj zgodnie z tą listą:

1. Zidentyfikuj i zabij długie transakcje: Jeśli sekcja 3 skryptu pokazuje transakcję trwającą od wielu godzin, użyj KILL <session_id>. Bez tego Version Store nie zostanie wyczyszczony.
2. Wyczyść cache:
   • CHECKPOINT; -- Wymuś zapisanie brudnych stron
   • DBCC FREEPROCCACHE;
   • DBCC FREESYSTEMCACHE ('ALL')
   • DBCC DROPCLEANBUFFERS;
   • DBCC FREESESSIONCACHE;
3. Wykonaj Shrink:
   • -- Zmień 'tempdev' na logiczną nazwę swojego pliku
   • DBCC SHRINKFILE (N'tempdev' , 1024);
4. Ostateczność – Restart: Jeśli powyższe kroki zawiodą, jedynym sposobem na całkowite zresetowanie tempdb jest restart usługi SQL Server.

Pamiętaj, że FREEPROCCACHE może spowodować chwilowy skok CPU, bo serwer będzie musiał skompilować nowe plany dla nadchodzących zapytań).

USE [tempdb]
GO

-- 1. Opcjonalne: Próba wymuszenia zapisu stron na dysk
CHECKPOINT;

-- 2. Czyszczenie pamięci (uwaga na wydajność!)
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DBCC FREESYSTEMCACHE ('ALL');
DBCC FREESESSIONCACHE;

-- 3. Właściwe zmniejszanie pliku (logicznego imienia pliku)
-- Przykład dla pliku o nazwie tempdev do rozmiaru 1024MB
DBCC SHRINKFILE (N'tempdev' , 1024);
GO

4. Ważne ostrzeżenia (Best Practices)

Nie rób tego codziennie. Regularne shrinkowanie bazy danych (szczególnie tempdb) prowadzi do fragmentacji plików na poziomie systemu operacyjnego oraz marnuje zasoby CPU na czyszczenie cache.

• Restart SQL Server: To najskuteczniejszy sposób na wyczyszczenie tempdb, ponieważ ta baza jest tworzona od nowa przy każdym starcie usługi. Jeśli SHRINKFILE z czyszczeniem cache nie pomaga, restart jest ostatecznym rozwiązaniem.
• Ustal odpowiedni rozmiar docelowy: Zamiast shrinkować do minimum, ustaw Initial Size plików tempdb na taką wartość, która realnie obsługuje Twój dzienny ruch. Zapobiegniesz dzięki temu ciągłemu rozszerzaniu się plików (Autogrow), co jest kosztowne wydajnościowo.