We have a SQL Server 2012 Enterprise SP1 running our ERP system. We have 15 production databases with approx 570GB in size. Our Tempdb has an initial size of 100GB and sits on a 450GB drive. Every day it seems to grow in size until all 450GB is consumed in approx. 2-3 weeks. I have tried numerous SQL statement to try an isolate what is using space but every query seems to return little results and show not queries with any high allocation. For example
SELECT TS.session_id ,
CAST(TS.user_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) [Total Allocation User Objects MB] ,
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15, 2)) [Net Allocation User Objects MB] ,
CAST(TS.internal_objects_alloc_page_count / 128 AS DECIMAL(15, 2)) [Total Allocation Internal Objects MB] ,
- TS.internal_objects_dealloc_page_count ) / 128 AS DECIMAL(15,
2)) [Net Allocation Internal Objects MB] ,
+ internal_objects_alloc_page_count ) / 128 AS DECIMAL(15, 2)) [Total Allocation MB] ,
- TS.user_objects_dealloc_page_count ) / 128 AS DECIMAL(15, 2)) [Net Allocation MB] ,
T.text [Query Text]
FROM sys.dm_db_task_space_usage TS
INNER JOIN sys.dm_exec_requests ER ON ER.request_id = TS.request_id
AND ER.session_id = TS.session_id
OUTER APPLY sys.dm_exec_sql_text(ER.sql_handle) T
returns only 34 rows and all of them show an allocation of 0. However if I go to the shirk files of tempdb it shows that only 2% of the allocated space is available. I have tried every variation of the SQL script I can find on Google but nothing shows any allocation. Some show additional rows for example one shows 277 rows with indication on what is running vs no currently executing but the allocated space is still 0. Anyone have any suggestion or tricks they have used to track down issue in tempdb?
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.