question

Rizwan avatar image
Rizwan asked

Tempdb become full

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.
sql-server-2008-r2sql server 2008 r2tempdbsql-server-2008-r2-sp1
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.