TempDB data file is Huge

Question

TempDB data file is Huge

sunny.tjk

SSCertifiable

Points: 5041
More actions
May 27, 2010 at 11:11 am

#147241

Tempdb data file is around 370Gig and log file is aroung 8 Gig.
What do I do to get these files to normal size?
How do I know which processes are using Tempdb space?
I know restarting sql services will get them to normal but is there another way to do it?
Thanks everyone.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

If tempdb has grown this large it is because it needed the additional space to complete the work requested. What type of processing is being done on the server?

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Any large queries, hitting lots of data, can use tempdb for the intermediate process.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Books Online has a whole topic devoted to the problem. Take a look.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

laveen.bondugula SSC Enthusiast Points: 169 More actions · Answer 4

Set the tempdb datafile to autogrowth and disk should have enough space to handle future need of tempdb.If this cannot be done each time the sql server gets start tempdb gets created.

Regards,

Laveen Bondugula

Database Analyst

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

laveen.bondugula (5/27/2010)
Drop the database and recreate it.
Regards,
Laveen Bondugula
Database Analyst

Drop what database, tempdb? What does that solve?

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

laveen.bondugula (5/27/2010)
Drop the database and recreate it.
Regards,
Laveen Bondugula
Database Analyst

Fortunately, unlike SQL Server 6.5, every version of SQL Server since version 7 prevents you from dropping the tempdb (trust me on this, lesson learned the hard way). So you couldn't drop tempdb, nor should you.

Msg 3708, Level 16, State 5, Line 1
Cannot drop the database 'tempdb' because it is a system database.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

laveen.bondugula (5/27/2010)
Drop the database and recreate it.
Regards,
Laveen Bondugula
Database Analyst

And please do tell how you plan to drop TempDB?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

The cause of the growth needs to be determined. Here is a method I have used successfully to determine the cause of such growth.

http://www.sqlservercentral.com/articles/Log+growth/69476/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

sunny.tjk (5/27/2010)
Tempdb data file is around 370Gig and log file is aroung 8 Gig.
What do I do to get these files to normal size?
How do I know which processes are using Tempdb space?
I know restarting sql services will get them to normal but is there another way to do it?
Thanks everyone.

There's really only a couple of things that will cause that type of growth even on a fairly large system and I've found the most common thing to be a "Cross Join". The Cross Join is likely an "accidental" Cross Join that looks like a regular ol' Inner Join but one that produces a many-to-many join because of inadequate criteria, bad data, poor design, or just a huge misunderstanding of the data in the tables. The only way to find it is to shrink TempDB (bounce the service if you can't shrink it, can't be helped) and then use something like the articles that Jason and Grant provided a links for (you NEED to read those articles). If it turns out to be an ad hoc query that some user is running, you need to get a copy of the query. Whether it's ad hoc or an installed stored proc, you need to analyze the estimated execution plan and look for some fat arrows that have rowcounts that exceed the size of the tables they're derived from. Then you need to analyze the code, find the problem, and fix it.

Most queries of the nature that I'm speaking of are "all-in-one" queries with a whole lot of joins and usually the best way (ironically) is to break it up into smaller pieces possibly using a Temp Table to hold some much small interim results to join on. People have to remember that high performance, set based code doesn't mean "all in one query".

BTW... the code may also be "embedded code" found in GUI code or it may be in the form of "HQL" from Hibernate, nHibernate, or something similar. In any case, you need to get a copy of the query with all the parameters in place and start troubleshooting the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Feeg SSCrazy Points: 2766 More actions · Answer 10

hi go to http://www.sqlservercentral.com/Forums/Topic910208-146-1.aspx there are a few techniques in the link to check whats up with the tempdb.

If you searched on this forum you would have picked up a few more as well.

MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 11

Refer to the article belowto narrow down on the queries that are casuing the tempdb to grow.

http://strictlysql.blogspot.com/2010/03/whats-causing-my-tempdb-to-grow-sql_17.html

Regards,
Raj

http://Strictlysql.blogspot.com