Data Warehousing and Business Intelligence

3 September 2010

Q&A on Resizing TempDB

Filed under: SQL Server — Vincent Rainardi @ 5:43 pm
Tags: ,

Q: Can we change the growth increment of tempdb from 10% to 1 GB?

A: Yes. To do this, stop SQL Server, on command prompt: sqlservr -c -f. Doing this reset the TempDB data file to 2,304 KB (not 1 MB as in KB 307487) and the log file to 504 KB. The growth is reset to 10% MB for data and 1 MB for log. SQL is in single user mode, so no risk of TempDB corruption. No, from Config Mgr you can’t see that SQL Server is running. Then do this from SSMS:

ALTER DATABASE tempdb MODIFY FILE (NAME = ‘tempdev’, FILEGROWTH = 1GB)

Verify the growth increment before and after by issuing “sp_helpdb tempdb”. The growth column for the log file should be 10% before and 1024KB after.

Control-C on the command prompt to stop SQL Server. Restart SQL Server from Config Mgr.

Q: Can we shrink the TempDB?

A: Yes, we can. Same as above. Stop SQL Server and restart in single user mode on command prompt. But we specify the size this time, like this:

ALTER DATABASE tempdb MODIFY FILE (NAME = ‘tempdev’, SIZE = 3GB)
ALTER DATABASE tempdb MODIFY FILE (NAME = ‘templog’, SIZE = 1GB)

Note: we can only go higher but can’t go lower. For example, after setting it to 5 GB we can set it to 6 GB but we can’t set it to 4 GB. If we try to set it to 4GB it will remain 5GB.

We can specify the growth and the size at the same time.

Q: My TempDB is 200GB. If I shrink it to 1 GB will it come back to 200 GB?

A: If you execute the same queries against the same data, yes. If the big query that caused the TempDB to blow to 200GB is only a one off, no.

Q: Is it worth shrinking TempDB?

A: Depends on your disk space. If you have ample disk space then you shouldn’t worry. But question like this usually comes up because the disk space is limited. One point I’d like to make is, if you are working with a 1TB warehouse and running queries against a 1-billion-rows fact table, with joins to 5 dims and “sum group by” etc, don’t be surprise if the TempDB is 200GB. True that most SQL DBAs who never administered a SQL warehouse would be surprised, as in their books a TempDB “should be” around 5-10GB. Yes, the TempDB of a SQL Server used for warehousing is different to if it’s used for “data entry” applications (known as “transaction systems” in the BI world).

If you are working with a SQL warehouse, the question should be more of: how do I optimise the TempDB performance rather than disk space, e.g. 1 TempDB file per processor core, placing TempDB files on different spindles, putting them on RAID 1+0 rather than RAID 5, etc. If your company can’t buy a 1TB disk space then they probably shouldn’t do data warehousing. Almost by definition, data warehousing is about disk space.

Q: Can I shrink TempDB without stopping SQL Server?

A: Yes. Use dbcc alter file/db. Consider the risk of data corruption. This risk does look scary for prod DBs but may be acceptable for dev.

Advertisements

1 Comment »

  1. check out: http://msdn.microsoft.com/en-us/library/ms175527.aspx

    in a nut shell, tempdb datafiles should be of a fixed size. general rule of thumb, one file per cpu core (typically no more than 8, there are other articles that explain that), the files should be on a different device away from user databases… a basic guideline for sizing ‘could’ be size of memory allocated to the SQL Server, more often it will be based on sorting requirements, typically driven by the size of your larger indexes that you perform regular maintenance on…

    SORT_IN_TEMPDB = { ON | OFF }
    Specifies whether to store sort results in tempdb. The default is OFF.

    http://msdn.microsoft.com/en-us/library/ms186869.aspx

    shrinking tempdb data files is not really that big of a deal, since the structure is rebuilt when SQL Server restarts, however… read on…

    another general statement: shrinking database data files on a regular basis, not a good practice, see: http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

    Paul explains it better than i can…

    hope this helps 🙂

    Comment by Joe Burdette — 28 February 2011 @ 2:56 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: