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.


Martin, your query returns the following:

usr_obj_kb|internal_obj_kb| version_store_kb| freespace_kb  | mixedextent_kb
6272      |1984        | 64              | 30418048      | 4416
  • you may get more feedback at serverfault.com
    – user247245
    Commented Feb 28, 2011 at 22:18
  • It won't shrink of its own accord if the reason for it growing to that size has now passed. Do you have snapshot isolation enabled on that instance? Maybe a long running transaction meant the version store couldn't be purged at some point. Commented Feb 28, 2011 at 22:30

3 Answers 3


You should understand how SQL Server data storage works. It isn't like you are used to with client applications.

For performance and other reasons, SQL Server reserves blocks of space on the HD (in the MDF file) for the each database's use (including tempDB). That space as seen by the file size is still reserved even if it is not actively being used.

Whenever the database exceeds the allocated space (depending on the file growth settings), it may grow the amount of reserved space (never to give it back). So 30GB is either the initially reserved space or you did some huge operation at some point that caused it to grow really big.

You can use sp_spaceUsed to see how much of that file is actually in use.
You can shrink the database back to size (if it isn't all used) with DBCC_SHRINKDATABASE
As tempting as it may be, don't turn on the DB's AutoShrink property.

As for the last modified date, here is what I found:

The modified dates change when SQL Server closes the files (SQL Server shut down or the database is detached), or when the file is grown (either automatically or manually). All other times, SQL Server essentially bypasses the file system when performing the writes, and so the modified dates aren't updated.

  • Very interesting, so when to the "last modified" dates changes on an MDF file? Is it not every time you write something to the DB?
    – Abe Miessler
    Commented Feb 28, 2011 at 22:52
  • @Abe Miessier - Updated my answer to address that.
    – JohnFx
    Commented Feb 28, 2011 at 23:02

It won't shrink of its own accord if the reason for it growing to that size has now passed.

Do you have snapshot isolation enabled on that instance?

What does the following query show?

SELECT SUM (user_object_reserved_page_count) * 8     as usr_obj_kb,
       SUM (internal_object_reserved_page_count) * 8 as internal_obj_kb,
       SUM (version_store_reserved_page_count) * 8   as version_store_kb,
       SUM (unallocated_extent_page_count) * 8       as freespace_kb,
       SUM (mixed_extent_page_count) * 8             as mixedextent_kb
FROM   sys.dm_db_file_space_usage  
  • I updated my question with the results of your query. Any chance you could give me a quick overview of what it all means?
    – Abe Miessler
    Commented Feb 28, 2011 at 22:44
  • @Abe - Well now it looks pretty much empty so I guess the only question is did it auto grow to 30GB (and if so why?) or is that the startup size for tempdb on that instance? Commented Feb 28, 2011 at 22:51
  • We were doing a SharePoint import on that DB and I suspect that something during that blew up the size of the temp db. The startup size for tempdb is much smaller.
    – Abe Miessler
    Commented Feb 28, 2011 at 22:53
  • @Abe - If it was just a one off then probably worth shrinking it and keeping an eye on it to. You might want to make sure that your instance is using instant file initialization as without this autogrow events can take a looong time. Commented Feb 28, 2011 at 23:02

TempDB is a temporary database that is required by SQL Server. You are correct in saying that it is used for holding temporary objects. They can range from #tables, @tables, to index rebuilds (in enterprise edition 2005 +), or table spools during query execution.

You CANNOT drop it, or detach it or take it offline. The only way to clear out tempdb if it is an issue for you, is to restart the SQL service. Everytime SQL Service is restarted, tempdb is re-created.

Unless your space is an issue, you dont have to worry about the size of tempdb. It probably grew that big because of some index rebuild maintenance jobs or massive bulk imports etc.


There are limitations for use of the DBCC SHRINKDATABASE command on the tempdb database. The target size for data and log files cannot be smaller than the size specified when the database was created or the last size explicitly set with a file-size changing operation such as ALTER DATABASE with the MODIFY FILE option or the DBCC SHRINKFILE command. Another limitation of DBCC SHRINKDATABASE is the calculation of the target_percentage parameter and its dependency on the current space used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.