Solved

SQL Server tempdb size increased suddenly

Posted on 2014-02-15
5
2,026 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

22 Experts available now in Live!

Get 1:1 Help Now