free tempdb

Question

Post reply

free tempdb

giri10488

Right there with Babe

Points: 729
More actions
June 30, 2015 at 2:39 pm

#318642

what is the best way to make free space in tempdb?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Drop all temp tables that are no longer needed.

Close all connections that are no longer needed.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 2

Add more disk.

--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)

giri10488 Right there with Babe Points: 729 More actions · Answer 3

how we come to know that temp tables & connection not needed

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

tempdb is like a scratch pad, so it will be filled up with many unnecessary stuff.

Create temp table (#) wisely. If not required, don't perform sorting operations on large tables, etc to avoid heavy usage of tempdb as these all operations tend to fill up/occupy tempdb files (data and log).

Perhaps, when it comes to a question of cleaning up files, shrink it in smaller chunks.

Best suggestion would be to "add space".

Thanks.

ScottPletcher SSC Guru Points: 100952 More actions · Answer 5

You can add a file to tempdb if you need to. Or explicitly expand an existing file(s) yourself.

Be extremely careful if/when trying to modify, or drop, existing objects in tempdb. It's very difficult to quickly accurately determine which ones are in use or not.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.