?
Solved

SQL Server tempdb size increased suddenly

Posted on 2014-02-15
5
Medium Priority
?
2,281 Views
Last Modified: 2014-03-03
Suddenly my tempdb size increased form 5 GB to  55 GB.
Why ? . How do I reduce the size ?
0
Comment
Question by:Varshini S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 400 total points
ID: 39861535
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
 
LVL 23

Assisted Solution

by:Patrick Bogers
Patrick Bogers earned 400 total points
ID: 39861561
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
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 400 total points
ID: 39861701
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
 
LVL 15

Assisted Solution

by:Deepak Chauhan
Deepak Chauhan earned 400 total points
ID: 39862328
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 400 total points
ID: 39865422
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question