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: 1004406 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: 101120 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".