SQL Server tempdb question

Question

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.

UPDATE:

Martin, your query returns the following:

usr_obj_kb|internal_obj_kb| version_store_kb| freespace_kb  | mixedextent_kb
6272      |1984        | 64              | 30418048      | 4416

Answer 1

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.

Tips:
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.

Answer 2

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  

Answer 3

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.

EDIT:

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.