SQL Server 2017 -- How to properly resize TEMPDB after it has AUTOGROWN

DJAdan 671 Reputation points
2020-09-28T16:46:58.08+00:00

HI All,

I would like some advice on how to properly resize TEMPDB after a run-away query caused tempdb to autogrow. Fortunately we caught it before we ran out of diskspace, but not before we came dangerously close to running out.

Our setup:

  1. We have 8x TEMPDB files (tempdevtemp2 thru temp8) initially sized at 4096MB, with 64MB Autogrowth, Unlimited size.
  2. Recently it grew to 12608MB/file. We have eliminated the scenario that caused the rapid growth.
  3. Currently our application is consuming less than 1% of TEMPDB.
  4. We would like to restart the server and have it resized to an initial size of 8192MB/file.

What is the recommended approach to accomplish this?

Ideally, SQL Server restart would bring it up at 8192MB/file. However, I don't see an obvious way to accomplish that, since I can't easily SHRINK it to 8192 and be guaranteed that every file would have that setting before the restart. In the best of worlds, we would have more space available to us, but we are living within our constraints.

Thank you for any assistance.

--Dan

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,760 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions
0 comments
Accepted answer
  1. tibor_karaszi@hotmail.com 4,301 Reputation points
    2020-09-29T06:52:47.593+00:00

    The key to understanding tempdb is that at startup of SQL Server, it is created based on a "template" found in sys.master_files. This determines what your tempdb will look like at startup. Unfortunately, the SSMS GUI doesn't reflect the fact that tempdb is different from all other databases, hence it is confusing.

    I've written a blog post about managing tempdb, hopefully sorting out the mess that this re-use of GUI causes. You'll find it here: http://sqlblog.karaszi.com/managing-tempdb/

    1 person found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. m 4,271 Reputation points
    2020-09-29T02:01:14.08+00:00

    Hi @DJAdan ,

    And then, on subsequent SQL Server restart, the new settings will be established? Is this correct?

    Yes. You are correct. Tempdb size resets after a SQL Server service restart.
    After the SQL Server service is restarted, you will see the tempdb size will be reset to the last manually configured size specified in DMV sys.master_files.

    More information: overview-of-the-shrink-tempdb-database-in-sql-server

    BR,
    Mia

    If the answer is helpful, please click "Accept Answer" and upvote it.

    1 person found this answer helpful.
    0 comments
  2. DJAdan 671 Reputation points
    2020-09-29T11:54:07.103+00:00

    Thank you Tibor.

    Your blog gives a very clear explanation of what I'm observing. The scenario you describe, SQL Server 2017, SSMS 17.8.1 is exactly the setup I have!

    I had come to the conclusion thanks to everyone's helpful comments, but the blog post puts it all in one place.

    Thanks again.

    --Dan

    1 person found this answer helpful.
    0 comments
  3. Jayson Sainsbury 26 Reputation points
    2020-09-28T17:32:42.317+00:00

    I personally have adopted Brent Ozar's methodology which can be found here in the link below. Essentially boils down to give the TempDB its own drive and size the files equally to fill the drive with no autogrowth. I have implemented this across the board in my environment and I have had no issues.

    Once you have set the options in SSMS for the initial size and growth you want, when you restart the SQL service it will reinitialize at the size you specified. I will say that if you insist on keeping autogrowth you might want to set it to higher than 64MB as that can cause cause a high vlf count and degrade performance.

    0 comments
  4. DJAdan 671 Reputation points
    2020-09-28T17:45:29.347+00:00

    Thank you Jayson,

    I am familiar with the recommendations.

    My question is, can I set the initial size value using SSMS to a value LOWER (8192) than it's current run value (12608) , but greater than the restart value (4096) and then restart my server so that it will actually implement those settings?

    This is a production system, and I have very limited opportunities for downtime, so I want to make sure I get it right.

    Thanks again.

    --Dan

    0 comments