grzech.webio.pl


.. kolejny blog w sieci (głównie na własne potrzeby)


Zapytania wykorzystujące bazę tempdb

SELECT SPID = s.session_id, s.[host_name], s.[program_name], s.status, s.memory_usage, granted_memory = CONVERT(INT, r.granted_query_memory*8.00), t.text, sourcedb = DB_NAME(r.database_id), workdb = DB_NAME(dt.database_id), mg.*, su.* FROM sys.dm_exec_sessions s INNER JOIN sys.dm_db_session_space_usage su ON s.session_id = su.session_id AND su.database_id = DB_ID('tempdb') INNER JOIN sys.dm_exec_connections c ON s.session_id = c.most_recent_session_id LEFT OUTER JOIN sys.dm_exec_requests r ON r.session_id = s.session_id LEFT OUTER JOIN ( SELECT session_id, database_id FROM sys.dm_tran_session_transactions t INNER JOIN sys.dm_tran_database_transactions dt ON t.transaction_id = dt.transaction_id WHERE dt.database_id = DB_ID('tempdb') GROUP BY session_id, database_id ) dt ON s.session_id = dt.session_id CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) t LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg ON s.session_id = mg.session_id WHERE (r.database_id = DB_ID('tempdb') OR dt.database_id = DB_ID('tempdb')) AND s.status = 'running' ORDER BY SPID; Źródło: http://sqlblog.com/blogs/kevin_kline/archive/2007/10/23/tempdb-space-usage.aspx