Solved

SQL Server tempdb size increased suddenly

Posted on 2014-02-15
5
2,055 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
5 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 100 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 19

Assisted Solution

by:Patricksr1972
Patricksr1972 earned 100 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 100 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:deepakChauhan
deepakChauhan earned 100 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:
ScottPletcher earned 100 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now