In prod cluster sql server 2008R2 the tempdb drive size is 50 GB. After the deployement of one database the tempdb logfile became full very frequently. Atlast I have created another log tempdb log in other drive and it goes to 300 GB or sometime more. Please need your help how to track the cause so that I can approach to Application Team.
THis query is taken from MSDN Blogs: Monitoring tempdb Transactions and Space usage (
http://blogs.msdn.com/b/deepakbi/archive/2010/04/14/monitoring-tempdb-transactions-and-space-usage.aspx), and may help to see which sessions have taken up the space select sys.dm_exec_sessions.session_id as [SESSION ID], db_name(database_id) as [DATABASE Name], host_name as [System Name], program_name as [Program Name], login_name as [USER Name], status, cpu_time as [CPU TIME (in milisec)], total_scheduled_time as [Total Scheduled TIME (in milisec)], total_elapsed_time as [Elapsed TIME (in milisec)], ( memory_usage * 8 ) as [Memory USAGE (in KB)], ( user_objects_alloc_page_count * 8 ) as [SPACE Allocated FOR USER Objects (in KB)], ( user_objects_dealloc_page_count * 8 ) as [SPACE Deallocated FOR USER Objects (in KB)], ( internal_objects_alloc_page_count * 8 ) as [SPACE Allocated FOR Internal Objects (in KB)], ( internal_objects_dealloc_page_count * 8 ) as [SPACE Deallocated FOR Internal Objects (in KB)], case is_user_process when 1 then 'user session' when 0 then 'system session' end as [SESSION Type], row_count as [ROW COUNT] from sys.dm_db_session_space_usage inner join sys.dm_exec_sessions on sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id;