0

I am trying to resolve the disk space issue on one of my drives where tempdb residing occupies more than 100 GB: Stats shown below

Tempdb stats

I read some similar post on the same and i found few queries using the tempdb.

So i am just curious if there is a way i can identify the temporary tables and their usage:

To start, found a query from Olaf which listed the temp tables and the size allocated.

But the above result keeps on changing as temp tables may add and get removed. So i am wondering if there is a way i can track such temp tables and their usage via which i can help to keep the tempdb growth stable.

Community
  • 1
KASQLDBA
  • 7,159
  • 6
  • 27
  • 53
  • Also see http://sqlperformance.com/2014/05/t-sql-queries/dude-who-owns-that-temp-table and http://dba.stackexchange.com/q/61703/1186 – Aaron Bertrand Dec 23 '14 at 12:40
  • I went through those articles found few queries which could be the culprit for increasing tempdb size.. However for a temporary fix i would like to know, if i can move two of the secondary files say tempdev2 and tempdev3 onto the different drives. since all the 4 files of tempdb resides on a single drive along with other DB;s data files, causing my drive full. if yes, what safety should i take? – KASQLDBA Dec 24 '14 at 12:45
  • Also the point i've notice is that amount of unallocated space is 50751.37 MB which seems quite large – KASQLDBA Dec 24 '14 at 12:49
  • It's very odd to have an odd number of data files. Why aren't there 4 data files and 1 log file? 2. Yes, you should try and isolate tempdb I/O from other I/O as much as possible. Easiest would probably be to drop the two additional data files, then re-create all 4 in a better location, making sure that they all have the same file size and growth rate. Also consider setting global trace flags 1117 and 1118.
    • – Aaron Bertrand Dec 24 '14 at 15:19