locked
Temp DB space issue RRS feed

  • Question

  • Hello

    I have two instances fo SQL Server ruuning in a test enviornment with totally 60 GB. Both has multiple  files and inital size is 2GB per file. But the OS shows only 2.5 GB free space left. So when i ran the below query and looks like temp DB is not using that much space, then why ony 2.5 GB free on that drive. If any one can explain that will be great!!!!

    SELECT SUM(unallocated_extent_page_count) AS [free pages],
    (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
    FROM sys.dm_db_file_space_usage;

    free pages      free space in MB
    2085600        16293.750000 

    free pages      free space in MB
    3587672        28028.687500

    Thank you!!!

     

    Thursday, October 9, 2014 5:39 PM

Answers

  • Presumably, at some point you needed that much tempdb space. It's not that tempdb grows and shrinks, if that that is what you expected.

    Assuming, of course, that it is really tempdb that is eating your disk. That is not obvious from your post.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 9, 2014 9:39 PM

All replies

  • Hi,

    This clearly shows both the instances combined tempdb physical file utilization is approx. 44 GB (16 GB +  28 GB). There is some process which utilizing tempdb and causing the file growth. Even if it says free space in MB the physical file size on the OS grown and taking 44 GB rest of them may be data file and other files.

    If this is not production and if you can recycle the instance you will regain the tempdb size back to 2 GB and free up OS space. Please set tempdb file growth restricted to 6 GB size and see which process causing tempdb growth. Once it reach 6GB mark the process will fail or you need to manually set up monitor with above query to a temp table to track tempdb growth.

    Thanks,

    -SreejitG


    • Edited by Sreejith G Thursday, October 9, 2014 6:03 PM
    Thursday, October 9, 2014 6:01 PM
  • Presumably, at some point you needed that much tempdb space. It's not that tempdb grows and shrinks, if that that is what you expected.

    Assuming, of course, that it is really tempdb that is eating your disk. That is not obvious from your post.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 9, 2014 9:39 PM