Tempdb

  • giri10488

    Say Hey Kid

    Points: 699

    Hi All,

    We are facing Temp DB full issue at our organisation. for that I want to perform below steps

    tempdb getting full so I have to divide the tempdb= no of core like 4 tempdb & the setting 

    3 datafile = None auto growth setting

    last 1 datafile = 10% auto growth

    This would resolve tempdb getting full issue but I need Microsoft approved document for this resolution. I have done this setting in my previous environments so many server & I believe this would resolve the issue definitely. 

    Can some one help me getting any document or article from Microsoft for this fix. 

    Thanks 

    Giriraj

  • Jeff Moden

    SSC Guru

    Points: 993661

    giri10488 - Tuesday, December 12, 2017 12:00 PM

    Hi All,

    We are facing Temp DB full issue at our organisation. for that I want to perform below steps

    tempdb getting full so I have to divide the tempdb= no of core like 4 tempdb & the setting 

    3 datafile = None auto growth setting

    last 1 datafile = 10% auto growth

    This would resolve tempdb getting full issue but I need Microsoft approved document for this resolution. I have done this setting in my previous environments so many server & I believe this would resolve the issue definitely. 

    Can some one help me getting any document or article from Microsoft for this fix. 

    Thanks 

    Giriraj

    How much total drive space does TempDB grow to?

    Also, that 10% auto-growth thing... that's going to kill you.  Both the MDF and LDF files need to be set to a fixed amount of MB and the initial size needs to be something that you would normally expect each file to be.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Scott Coleman

    One Orange Chip

    Points: 27309

    Are any of the new tempdb data files on a different disk?  Too much data to fit on the disk is still going to be too big when split into four files on the same disk.

  • giri10488

    Say Hey Kid

    Points: 699

    Total 250 GB space on tempdb consuming whole drive..i have divide the tempdb & its working fine now without any issues. I just need the microsoft document to understand more that how dividing tempdb will resolve the space issue

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Dividing TempDB won't do anything about space problems. If TempDB is 250GB, it's 250GB, doesn't matter if it's 1 file of 10.

    If TempDB is getting full, your options are to make it larger, or identify code that's using TempDB heavily and change it so that it's not using as much.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sue_H

    SSC Guru

    Points: 89728

    giri10488 - Tuesday, December 12, 2017 3:52 PM

    Total 250 GB space on tempdb consuming whole drive..i have divide the tempdb & its working fine now without any issues. I just need the microsoft document to understand more that how dividing tempdb will resolve the space issue

    Microsoft's documentation about increasing the number of tempdb files is to address contention issues, not space issue. Here is the documentation about that along with links to a few other articles on the same subject of number of tempdb files:
    Recommendations to reduce allocation contention in SQL Server tempdb database

    Sue

  • giri10488

    Say Hey Kid

    Points: 699

    But it happens with me i have done on so many servers & resolve the tempdb issue but we have to configure it properly as i have written above. you can also test it & will definately see that it will resolve space issue.

  • Sue_H

    SSC Guru

    Points: 89728

    giri10488 - Tuesday, December 12, 2017 4:20 PM

    But it happens with me i have done on so many servers & resolve the tempdb issue but we have to configure it properly as i have written above. you can also test it & will definately see that it will resolve space issue.

    Sorry but it does not affect space. There is no documentation for it resolving space issues as it doesn't do that.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 993661

    giri10488 - Tuesday, December 12, 2017 4:20 PM

    But it happens with me i have done on so many servers & resolve the tempdb issue but we have to configure it properly as i have written above. you can also test it & will definately see that it will resolve space issue.

    I have a couple of Terabyte databases on one server.  It not only is subject to a fair bit of OLTP, but also has huge batch file runs for ETL, the related validations, and much more.  We have a total of 16GB in eight 2GB files for TempDB and the log file is also 2GB.  They've NEVER grown.

    If you have 250GB of TempDB, you have some really, really bad code that needs to be identified and fixed.  You also need to fix the log file growth to be a fixed amount rather than a percentage.  No amount of trickery is going to solve this problem.  You need to find the code that's doing this and fix it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Gail Shaw

    SSC Guru

    Points: 1004424

    giri10488 - Tuesday, December 12, 2017 4:20 PM

    you can also test it & will definately see that it will resolve space issue.

    No, it won't. It can't, because a 250GB tempDB is 250GB, no matter how many data files there are.

    Now, if you went and took a 10GB TempDB and added 3 more 10GB files, then sure, that will resolve space issues, but you could have increased the single file to 40GB for the same effect.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply