Chciałbym wiedzieć, jak zidentyfikować dokładne zapytanie lub przechowywany proc, który faktycznie wypełnia dziennik transakcji bazy danych TEMPDB.

Od http://www.sqlservercentral.com/scripts/tempdb/72007/

;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text, 
                 ERQ.statement_start_offset / 2, 
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset 
                  THEN 0 
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;

EDYTOWAĆ

Jak zauważył Martin w komentarzu, nie znalazłoby to aktywnych transakcji zajmujących miejsce w tempdb, znajdzie tylko aktywne zapytania , które obecnie zajmują tam miejsce (i prawdopodobnie sprawcy bieżącego użycia dziennika). Może więc istnieć otwarta transakcja, ale faktyczne zapytanie, które powoduje problem, nie jest już uruchomione.

Możesz zmienić inner joinna sys.dm_exec_requestsa left outer join, a następnie zwrócisz wiersze dla sesji, w których aktualnie nie są aktywne zapytania.

Zapytanie Martin opublikował ...

SELECT database_transaction_log_bytes_reserved,session_id 
  FROM sys.dm_tran_database_transactions AS tdt 
  INNER JOIN sys.dm_tran_session_transactions AS tst 
  ON tdt.transaction_id = tst.transaction_id 
  WHERE database_id = 2;

... identyfikuje session_ids z aktywnymi transakcjami, które zajmują miejsce w dzienniku, ale niekoniecznie będziesz w stanie określić rzeczywiste zapytanie, które spowodowało problem, ponieważ jeśli nie jest teraz uruchomione, nie zostanie przechwycone w powyższym zapytaniu dla aktywne wnioski. Być może będziesz w stanie odruchowo sprawdzić ostatnie zapytanie, DBCC INPUTBUFFERale może nie powiedzieć Ci, co chcesz usłyszeć. Możesz dołączyć zewnętrznie w podobny sposób, aby uchwycić osoby aktywnie działające, np .:

SELECT tdt.database_transaction_log_bytes_reserved,tst.session_id,
       t.[text], [statement] = COALESCE(NULLIF(
         SUBSTRING(
           t.[text],
           r.statement_start_offset / 2,
           CASE WHEN r.statement_end_offset < r.statement_start_offset
             THEN 0
             ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
         ), ''
       ), t.[text])
     FROM sys.dm_tran_database_transactions AS tdt
     INNER JOIN sys.dm_tran_session_transactions AS tst
     ON tdt.transaction_id = tst.transaction_id
         LEFT OUTER JOIN sys.dm_exec_requests AS r
         ON tst.session_id = r.session_id
         OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
     WHERE tdt.database_id = 2;

Możesz również użyć DMV, sys.dm_db_session_space_usageaby zobaczyć ogólne wykorzystanie przestrzeni przez sesję (ale znowu możesz nie uzyskać poprawnych wyników dla zapytania; jeśli zapytanie nie jest aktywne, to, co otrzymasz, może nie być faktycznym winowajcą).

;WITH s AS
(
    SELECT 
        s.session_id,
        [pages] = SUM(s.user_objects_alloc_page_count 
          + s.internal_objects_alloc_page_count) 
    FROM sys.dm_db_session_space_usage AS s
    GROUP BY s.session_id
    HAVING SUM(s.user_objects_alloc_page_count 
      + s.internal_objects_alloc_page_count) > 0
)
SELECT s.session_id, s.[pages], t.[text], 
  [statement] = COALESCE(NULLIF(
    SUBSTRING(
        t.[text], 
        r.statement_start_offset / 2, 
        CASE WHEN r.statement_end_offset < r.statement_start_offset 
        THEN 0 
        ELSE( r.statement_end_offset - r.statement_start_offset ) / 2 END
      ), ''
    ), t.[text])
FROM s
LEFT OUTER JOIN 
sys.dm_exec_requests AS r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;

Mając do dyspozycji wszystkie te zapytania, powinieneś być w stanie zawęzić listę osób korzystających z tempdb i tego, w jaki sposób, zwłaszcza jeśli złapiesz je na gorącym uczynku.

kilka wskazówek dotyczących minimalizacji wykorzystania tempdb

1. używaj mniej #temp tabel i zmiennych @table
2. zminimalizuj jednoczesną obsługę indeksu i unikaj tej SORT_IN_TEMPDBopcji, jeśli nie jest potrzebna
3. unikaj niepotrzebnych kursorów; unikaj kursorów statycznych, jeśli uważasz, że może to być wąskie gardło, ponieważ statyczne kursory używają tabel roboczych w tempdb - chociaż jest to typ kursora, który zawsze zalecam, jeśli tempdb nie jest wąskim gardłem
4. staraj się unikać buforowania (np. duże CTE, do których odwołuje się wiele razy w zapytaniu)
5. nie używaj MARS
6. dokładnie przetestuj użycie poziomów izolacji migawki / RCSI - nie włączaj go tylko dla wszystkich baz danych, ponieważ powiedziano ci, że jest lepszy niż NOLOCK (jest, ale nie jest darmowy)
7. w niektórych przypadkach może to zabrzmieć nieintuicyjnie, ale użyj większej liczby tabel tymczasowych. np. rozbicie ogromnego zapytania na części może być nieco mniej wydajne, ale jeśli może uniknąć ogromnego rozlania pamięci do tempdb, ponieważ pojedyncze, większe zapytanie wymaga przyznania zbyt dużej pamięci ...
8. unikaj włączania wyzwalaczy dla operacji masowych
9. unikaj nadużywania typów LOB (typy maksymalne, XML itp.) jako zmiennych lokalnych
10. transakcje powinny być krótkie i słodkie
11. nie ustawiaj tempdb jako domyślnej bazy danych wszystkich -

Możesz również wziąć pod uwagę, że użycie dziennika tempdb może być spowodowane wewnętrznymi procesami, nad którymi nie masz kontroli lub nie masz nad nimi żadnej kontroli - na przykład poczta w bazie danych, powiadomienia o zdarzeniach, powiadomienia o zapytaniach i broker usług w jakiś sposób korzystają z tempdb. Możesz przestać korzystać z tych funkcji, ale jeśli ich używasz, nie możesz dyktować, jak i kiedy używają tempdb.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/17d9f862-b9ae-42de-ada0-4229f56712dc/tempdb-log-filling-cannot-find-how-or-what?forum=sqldatabaseengine

 SELECT tst.[session_id],
            s.[login_name] AS [Login Name],
            DB_NAME (tdt.database_id) AS [Database],
            tdt.[database_transaction_begin_time] AS [Begin Time],
            tdt.[database_transaction_log_record_count] AS [Log Records],
            tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],
            tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],
            SUBSTRING(st.text, (r.statement_start_offset/2)+1,
            ((CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE r.statement_end_offset
            END - r.statement_start_offset)/2) + 1) AS statement_text,
            st.[text] AS [Last T-SQL Text],
            qp.[query_plan] AS [Last Plan]
    FROM    sys.dm_tran_database_transactions tdt
            JOIN sys.dm_tran_session_transactions tst
                ON tst.[transaction_id] = tdt.[transaction_id]
            JOIN sys.[dm_exec_sessions] s
                ON s.[session_id] = tst.[session_id]
            JOIN sys.dm_exec_connections c
                ON c.[session_id] = tst.[session_id]
            LEFT OUTER JOIN sys.dm_exec_requests r
                ON r.[session_id] = tst.[session_id]
            CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
            OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
    WHERE   DB_NAME (tdt.database_id) = 'tempdb'
    ORDER BY [Log Bytes Used] DESC
GO

Dziękuję za ten post, prawdopodobnie jedyny w swoim rodzaju. Mój test był prosty, utwórz tabelę tymczasową i upewnij się, że pojawi się, gdy uruchomię dowolne z zapytań z tego postu ... tylko jedno lub dwa naprawdę się powiodły. Poprawiłem go, aby dołączyć do T-SQL, zoptymalizowałem go do dłuższych uruchomień i sprawiłem, że był całkiem użyteczny. Daj mi znać, jeśli coś przeoczyłem, ale jak dotąd masz zautomatyzowany / zapętlony skrypt. Zapewnia sposób oceny, które zapytanie / SPID jest sprawcą w danym okresie przy użyciu zapytania o odchylenie standardowe (STDEV) poniżej.

Trwa to co 3 minuty 40 razy, więc 2 godziny. Zmodyfikuj parametry według własnego uznania.

Poniżej znajduje się filtr GDZIE> 50 stron, który ludzie mogą chcieć wyczyścić na wypadek, gdybyś miał dużo małych stolików. W przeciwnym razie nie złapiesz tego niuansu z poniższym, ponieważ jest ...

Cieszyć się!

DECLARE @minutes_apart INT; SET @minutes_apart = 3
DECLARE @how_many_times INT; SET @how_many_times = 40
--DROP TABLE tempdb..TempDBUsage
--SELECT * FROM tempdb..TempDBUsage
--SELECT session_id, STDEV(pages) stdev_pages FROM tempdb..TempDBUsage GROUP BY session_id HAVING STDEV(pages) > 0 ORDER BY stdev_pages DESC

DECLARE @delay_string NVARCHAR(8); SET @delay_string = '00:' + RIGHT('0'+ISNULL(CAST(@minutes_apart AS NVARCHAR(2)), ''),2) + ':00'
DECLARE @counter INT; SET @counter = 1

SET NOCOUNT ON
if object_id('tempdb..TempDBUsage') is null
    begin
    CREATE TABLE tempdb..TempDBUsage (
        session_id INT, pages INT, num_reads INT, num_writes INT, login_time DATETIME, last_batch DATETIME,
        cpu INT, physical_io INT, hostname NVARCHAR(64), program_name NVARCHAR(128), text NVARCHAR (MAX)
    )
    end
else
    begin
        PRINT 'To view the results run this:'
        PRINT 'SELECT * FROM tempdb..TempDBUsage'
        PRINT 'OR'
        PRINT 'SELECT session_id, STDEV(pages) stdev_pages FROM tempdb..TempDBUsage GROUP BY session_id HAVING STDEV(pages) > 0 ORDER BY stdev_pages DESC'
        PRINT ''
        PRINT ''
        PRINT 'Otherwise manually drop the table by running the following, then re-run the script:'
        PRINT 'DROP TABLE tempdb..TempDBUsage'
        RETURN
    end
--GO
TRUNCATE TABLE tempdb..TempDBUsage
PRINT 'To view the results run this:'; PRINT 'SELECT * FROM tempdb..TempDBUsage'
PRINT 'OR'; PRINT 'SELECT session_id, STDEV(pages) stdev_pages FROM tempdb..TempDBUsage GROUP BY session_id HAVING STDEV(pages) > 0 ORDER BY stdev_pages DESC'
PRINT ''; PRINT ''

while @counter <= @how_many_times
begin
INSERT INTO tempdb..TempDBUsage (session_id,pages,num_reads,num_writes,login_time,last_batch,cpu,physical_io,hostname,program_name,text)
    SELECT PAGES.session_id, PAGES.pages, r.num_reads, r.num_writes, sp.login_time, sp.last_batch, sp.cpu, sp.physical_io, sp.hostname, sp.program_name, t.text
    FROM sys.dm_exec_connections AS r
    LEFT OUTER JOIN master.sys.sysprocesses AS sp on sp.spid=r.session_id
    OUTER APPLY sys.dm_exec_sql_text(r.most_recent_sql_handle) AS t
    LEFT OUTER JOIN (
        SELECT s.session_id, [pages] = SUM(s.user_objects_alloc_page_count + s.internal_objects_alloc_page_count) 
        FROM sys.dm_db_session_space_usage AS s
        GROUP BY s.session_id
        HAVING SUM(s.user_objects_alloc_page_count + s.internal_objects_alloc_page_count) > 0
    ) PAGES ON PAGES.session_id = r.session_id
    WHERE PAGES.session_id IS NOT NULL AND PAGES.pages > 50
    ORDER BY PAGES.pages DESC;
PRINT CONVERT(char(10), @counter) + ': Ran at: ' + CONVERT(char(30), GETDATE())
SET @counter = @counter + 1
waitfor delay @delay_string
end

Niestety dziennika tempDB nie można bezpośrednio prześledzić z powrotem do sessionID poprzez przeglądanie uruchomionych procesów.

Zmniejsz plik dziennika tempDB do punktu, w którym wzrośnie on znacznie. Następnie utwórz rozszerzone zdarzenie, aby uchwycić wzrost dziennika. Gdy znów wzrośnie, możesz rozwinąć zdarzenie rozszerzone i wyświetlić plik zdarzenia pakietu. Otwórz plik, dodaj filtr czasu, filtr typu pliku (nie chcesz uwzględniać wyników pliku danych), a następnie zgrupuj go według identyfikatora sesji w SSMS. Pomoże to znaleźć winowajców, którzy szukają identyfikatorów sesji z największą liczbą grup według. Oczywiście musisz zebrać to, co działa w identyfikatorach sesji, za pośrednictwem innego procesu lub narzędzia. Może ktoś wie, jak uzyskać zapytanie z kolumny query_hash i będzie na tyle miły, że opublikuje rozwiązanie.

Wyniki rozszerzonego wydarzenia:

Skrypt do utworzenia rozszerzonego zdarzenia:

CREATE EVENT SESSION [tempdb_file_size_changed] ON SERVER ADD EVENT 
sqlserver.database_file_size_change(SET collect_database_name=(1)ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.is_system,sqlserver.query_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE ([database_id]=(2))) ADD TARGETpackage0.event_file(SET filename=N'C:\ExtendedEvents\TempDBGrowth.xel',max_file_size=(100),max_rollover_files=(25)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)