[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server tempdb size increased suddenly

Posted on 2014-02-15
5
Medium Priority
?
2,362 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 70

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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

649 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