SQL Server tempdb size seems large, is this normal?
From what I understand the system database is used to hold temporary tables, intermediate results and other temporary information.
On one of my database instances I have a tempdb that is seems very large (30GB). This database has not been modified (as in "last modified date" on the mdf file) in over a week. Is it normal to have the temp db remain that large for that long of a period? It seems to me that it should be updating fairly often and returning space that it is using fairly quickly...
Am I way off here or is SQL Server doing something weird?
FYI: This is a SharePoint 2010 database, not sure if that makes a difference.
Solution 1:
TempDB will not AUTOSHRINK, and you cannot set TempDB to AUTOSHRINK. If your TempDB grew to 30GB, it likely grew to that size for a reason, so if you do re-size it to be smaller, it will likely just grow to that size again.
Check out the following links for some suggestions for configuring TempDB:
Optimizing TempDB Performance
Capacity Planning for TempDB
Solution 2:
SQL Server database files don't shrink automatically, unless you tell them to. (And I wouldn't recommend it, since you'll get lots of disk fragmentation from repeated growing/shrinking.) In other words, find out how large your tempdb likes to be, and plan for that. Setting its data files to start out nice and large (since it's recreated from scratch when SQL Server starts) is generally the recommended approach.
Solution 3:
Although the file sizes are listed as 30GB, they may be filled with Empty Space and not actually contain 30GB of file size. TEMPDB would have grown to 30GB at some point because it needed that much space for some certain queries.
I would recommend you set the TEMPDB file size to 30GB - this will presize TEMPDB for you in case SQL SERVER is restarted since TEMPDB is recreated every time SQL Server is restarted. By pre-sizing TEMPDB you will avoid any of the over head related to the database autogrowing again during the activity that made it grow in the first place.
This page, http://sqlserverpedia.com/wiki/TempDB, in section 4 will go over pre-sizing temp db.