SQL Server tempdb size increased suddenly

Suddenly my tempdb size increased form 5 GB to  55 GB.
Why ? . How do I reduce the size ?
Varshini SAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Did the log grow or the data file(s)?

If the log grew, you can safely shrink that.

But there's realistically typically no safe way to shrink tempdb data while SQL is active.

Furthermore, if tempdb data grew before, it could grow again after you shrink it, and you will have just caused more overhead.

Until you figure out why tempdb grew, you need to:

1) find additional disk space for it
2) make sure all tempdb data files are the same size
0
 
Surendra NathConnect With a Mentor Technology LeadCommented:
There are various reasons why the tempdb can grow in size suddenly...

and there are 3 ways to reduce the tempdb Size

1) using TSQL
2) DBCC shrinkDataBase
3) DBCC shrinkFile

How to use them and detailed explanation of them are in the below KB article
http://support.microsoft.com/kb/307487
0
 
Patrick BogersConnect With a Mentor Datacenter platform engineer LindowsCommented:
IMHO best way is to put backup model to FULL and take a backup. This will delete all logs.
Next you can put it back to Simple mode depending on your preferences.
0
 
PadawanDBAConnect With a Mentor Operational DBACommented:
I think the previous two sufficiently answered how to shrink the TempDB.  As to the why did it grow, there are lots of reasons that it could grow.  The TempDB is responsible for all things temporary, for example: temp tables, table variables, cursors, and internal work tables for sorting/spooling.  When these objects cannot be stored in memory, they are persisted into the minimally logged TempDB.  If you are performing heavy workloads involving temp tables and/or sorting (monster queries in general can have their result sets moved into the TempDB for persistence as well), this data will get moved into your TempDB.  If your TempDB is not large enough to handle what needs to be temporarily persisted there, then it is off to the races and growing as it needs.  Shrinking it will certainly remedy the problem immediately, but you're going to want to make sure you find the root cause of what activity made the TempDB require so much space to begin with, otherwise it's just going to grow back to that size again.  If it's something that there is no avoiding, then you may as well right-size your TempDB to where it should be, as you're taking a performance hit on the auto grows (especially if you don't have instant initialization enabled for SQL Server).  Hope that helps!

If you want to read more on the TempDB: http://technet.microsoft.com/en-us/library/ms190768.aspx
0
 
Deepak ChauhanConnect With a Mentor SQL Server DBACommented:
First check which type of objects are consuming space in TempDB and then opt any appropriate solution. Like modify file size or shrink DB whatever is possible.

This is a script to identify which type of objects are taking much space

SELECT
SUM (user_object_reserved_page_count)*8 as user_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
------------
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.