Is there a way to know, from which database a temporary table has been created in TempDB?

Question

Post reply

Is there a way to know, from which database a temporary table has been created in TempDB?

Viewing 6 posts - 16 through 20 (of 20 total)

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

sanjeev_krs2004 Default port Points: 1462 More actions · Answer 1

sometimes because of memory issue otherwise space issue.

Thanks,

Sanjeev.

Resender SSCertifiable Points: 7443 More actions · Answer 2

Is this done often, cause I wouldn't recommend it.

I understand why you would want to erase the temp tables if their consuming much space, but rather then trying to find out which ones belong to which non-TempDB database I would look to the functions and stored procedures that use them.

Cause temp tables should always be dropped after the procedure of function that used them has finished processing.

steveb. SSC-Forever Points: 46733 More actions · Answer 3

sanjeev_krs2004 (7/5/2011)
sometimes because of memory issue otherwise space issue.
Thanks,
Sanjeev.

to be honest this sounds like an 'ambulance at the bottom of the cliff' solution , you should look at finding out what is causing your memory and space issues and address them rather than trying to look at solutions involving deleting the temp databases.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

steveb. (7/5/2011)
sanjeev_krs2004 (7/5/2011)
sometimes because of memory issue otherwise space issue.
Thanks,
Sanjeev.
to be honest this sounds like an 'ambulance at the bottom of the cliff' solution , you should look at finding out what is causing your memory and space issues and address them rather than trying to look at solutions involving deleting the temp databases.

Sanjeev,

Read the above. It's important. To emphasize, rebooting to solve memory and space issues is like putting a bandaid on a deep stab-wound... you've covered up the problem but you haven't stopped the bleeding and the patient (your DB server) will eventually die.

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

sanjeev_krs2004 Default port Points: 1462 More actions · Answer 5

I agree with you both...but TempDB is in shared environment ...I cant control everything there ...I can check my functions and SPs (I have already optimized SPs for this) but not other databases ...so was thinking to help my client as much as I could.

And to add here, I have learnt a new thing (my original question has been answered).

Resender SSCertifiable Points: 7443 More actions · Answer 6

Well to answer bluntly (no offfence) there is no way to learn it from the metadata.

temp tables are created in the TempDB, they are owned by it and are part of it, even if created in other databases.

temp tables should have a short life.

The only thing I could advice is that all databases that use that tempDb are checked, to make sure that they are no procedures that overuse or forget their temp tables. Look if possible to move tempdb to another machine (if possible) untill the culprit is found.

There 2 more likely scenarios

1)A temp table is created with a unique number (probably based on a timestamp) but not dropped

2)A temp table is created that is dropped at the end, but which is so huge its consuming to much resources