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,630 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 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. Erland Sommarskog 100.8K Reputation points MVP
    2020-09-28T21:47:37.03+00:00

    To set the size of a file use ALTER DATABASE MODIFY FILE. SQL Server will use that size on restart, as I recall.

    You talk about SSMS; I don't know what the UI in SSMS does as I would never user it. But you can use the Script button up to the left to get a script of the action you would get if you press OK.

    0 comments
  2. DJAdan 671 Reputation points
    2020-09-28T22:22:25.487+00:00

    Hi Erland,

    Thank you for your response.

    As I read further, it appears that ALTER DATABASE MODIFY FILE will allow me to properly size the files.

    What is not clear from reading the documentation, is that the behavior of ALTER DATABASE MODIFY FILE size behaves differently for TEMPDB vs USERDBs. With a USERDB, it appears that the ALTER DATABASE takes effect immediately. With a TEMPDB, if I resize the file to a smaller size, it will take effect on the next server restart. If this is the case, it is exactly what I want.

    So, on my running system I can issue in advance, without it taking effect: 

    ALTER DATABASE tempdb MODIFY FILE ( NAME = N'tempdev', SIZE = 8192MB , FILEGROWTH = 64MB );
ALTER DATABASE tempdb MODIFY FILE ( NAME = N'temp2',   SIZE = 8192MB , FILEGROWTH = 64MB );
....

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

    Thanks again.

    --Dan

    0 comments