Troubleshooting Insufficient Disk Space in tempdb

Następująca kwerenda zwraca wartość całkowita liczba wolnych stron i całkowita ilość wolnego miejsca w megabajtach (MB) dostępnej w wszystkie pliki w tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Następująca kwerenda zwraca wartość całkowitą liczbę stron używanych przez magazynu wersja oraz całkowitej ilości miejsca w MB używanych przez przechowywanie wersja w tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

Jeśli używa dużej ilości miejsca w magazynu wersja tempdb, należy określić, jaka jest najdłuższy bieżących transakcji.Ta kwerenda służy do wyświetlania listy aktywnych transakcji w kolejności, najdłuższego bieżących transakcji.

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

Długo działających transakcji, która nie jest związany z operacją indeksu online wymaga magazynu dużych wersja.Ten magazyn w wersja zachowuje wszystkie wersje generowany od momentu rozpoczęcia transakcji.Transakcje kompilacji online indeksu może zająć dużo czas na zakończenie, ale magazynu w oddzielnych wersja byłaby przeznaczona wyłącznie do operacji online indeksu jest używany.W związku z tym te czynności nie zapobiegają w wersjach od innych transakcji z jego usuwania.Aby uzyskać więcej informacji zobaczObciążenie zasób wersji wiersza.

Następująca kwerenda zwraca całkowitą liczbę stron używanych przez obiekty wewnętrzne i całkowitej ilości miejsca w MB używanych przez obiekty wewnętrzne tempdb.

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

Następująca kwerenda zwraca wartość całkowitą liczbę stron używanych przez użytkownika obiekty i całkowitą ilość miejsca używanego przez użytkownika obiekty w tempdb.

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

Następująca kwerenda zwraca wartość całkowitą ilość miejsca na dysku, które używane przez wszystkie pliki w tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

Poniższy przykład tworzy widok all_task_usage. Kwerendy, widoku Zwraca całkowitą ilość miejsca używanego przez wewnętrzne obiekty w wszystkich aktualnie uruchomionych zadań tempdb.

CREATE VIEW all_task_usage
AS 
    SELECT session_id, 
      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count 
    FROM sys.dm_db_task_space_usage 
    GROUP BY session_id;
GO

Poniższy przykład tworzy widok all_session_usage. Kwerendy, widoku zwraca miejsce używane przez wszystkie obiekty wewnętrzne uruchomiony i wykonane zadania w tempdb.

CREATE VIEW all_session_usage 
AS
    SELECT R1.session_id,
        R1.internal_objects_alloc_page_count 
        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
        R1.internal_objects_dealloc_page_count 
        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
    FROM sys.dm_db_session_space_usage AS R1 
    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO

Załóżmy, że podczas tych widoków są kwerendowane w odstępach czasu trzy minuty, zestawy wyników Podaj następujące informacje.

• W 17: 00 sesja 71 przydzielonych stron 100 i dealokowane 100 stron od rozpoczęcia sesji.

• At 5:03 P.M., session 71 allocated 20100 pages and deallocated 100 pages since the start of the session.

Podczas analizowania tych informacji, można stwierdzić, że między dwoma pomiarami: Sesja przydzielone 20 000 stron wewnętrzne obiekty i nie cofnąć przydział wszystkich stron. Wskazuje na potencjalny problem.

Aby ustalić partia, w którym jest uruchomiony w tym czasie, należy użyć Program SQL Server Profiler przechwytywaniaRPC: zakończone and SQL:BatchCompleted klas zdarzeń.

Zamiast SQL Server Profiler ma być uruchomiona DBCC INPUTBUFFER raz co trzy minuty, we wszystkich sesjach, jak pokazano w poniższym przykładzie.

DECLARE @max int;
DECLARE @i int;
SELECT @max = max (session_id)
FROM sys.dm_exec_sessions
SET @i = 51
  WHILE @i <= @max BEGIN
         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions
                    WHERE session_id=@i)
         DBCC INPUTBUFFER (@i)
         SET @i=@i+1
         END;

Sondowania z widoku all_query_usage, i uruchom następującą kwerendę do uzyskania tekst kwerendy:

SELECT R1.sql_handle, R2.text 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

Ponieważ sql_handle powinien być unikatowy dla każdej instancji unikatowy, nie trzeba zapisać duplikat sql_handle wpisów.

Aby zapisać plan uchwyt i planu XML, uruchom następującą kwerendę.

SELECT R1.plan_handle, R2.query_plan 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

Jako alternatywę do sondowanie sys.dm_exec_sql_text and sys.dm_exec_query_plan funkcji, można użyć SQL Server Profiler zdarzenia. Brak programu profilującego zdarzenia, które mogą być używane do planu kwerend i tekst kwerendy, który jest generowany.Na przykład zdarzenie 165 zwraca statystyki wydajności dla śledzenia, tekst SQL, planów kwerend i statystyk kwerendy.