Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
you may get more feedback at serverfault.com –  user247245 Feb 28 '11 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. –  Martin Smith Feb 28 '11 at 22:30
add comment

migrated from stackoverflow.com Jan 22 '12 at 14:38

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
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 Feb 28 '11 at 22:52
    
@Abe Miessier - Updated my answer to address that. –  JohnFx Feb 28 '11 at 23:02
add comment

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  
share|improve this answer
    
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 Feb 28 '11 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? –  Martin Smith Feb 28 '11 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 Feb 28 '11 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. –  Martin Smith Feb 28 '11 at 23:02
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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