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.
Best Answer
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