How to preserve global temporary table data

Question

Post reply

How to preserve global temporary table data

rajarshi_ghosh_05

Old Hand

Points: 348
More actions
March 29, 2013 at 1:05 pm

#272632

Hi guys,
I would like to know if anyway to preserve global temporary table data till some one explicitly drop it.
I have created one global ##tmp table and inserted data into it in one procedure (proc1) and selecting the data from another procedure (proc2).
If i execute the proc2 immediately after proc1 finished.. there is no issue but when i execute the same proc2 after few min later (for say around 15/20 min) i found that the global tmp table has been dropped!!!!
how to achieve the result without creating any physical table!!!
Thanks for your help....

Viewing 15 posts - 1 through 15 (of 48 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

even global temp tables get dropped if all the sessions that referenced it disconnect, i believe.

you'd need to start adding logic to create if not exists to your process, if you refuse to use a permanent table.

It really depends on what you are using teh global temp table for.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rajarshi_ghosh_05 Old Hand Points: 348 More actions · Answer 2

Actually i am importing csv file in the database through sp. the temp table is required to keep the validation error messages. When user wants to see the error messages then i need to select the entire table rows from this temp table and send to user in excel. If there is any delay from user end the table drops!!!

Is there any way to keep the reference to the global tmp table till i explicitly dropped it!!!

Thanks

Lowell SSC Guru Points: 323504 More actions · Answer 3

the only sure fire way to keep the keep that global temp table around is by having the process which created the temp table keep it's connection open. that is the only way to guarantee that a global temp table will remain in scope.

It really sounds like a permanent table would be better; especially for situations where the table gets created Friday, but the first person in the office Monday used an app that expected that info to still be there.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rajarshi_ghosh_05 Old Hand Points: 348 More actions · Answer 4

The highest time to keep the table alive is 120min.

Is it possible to keep the table alive by locking it or anything else?

Lowell SSC Guru Points: 323504 More actions · Answer 5

rajarshi_ghosh_05 (3/29/2013)
The highest time to keep the table alive is 120min.
Is it possible to keep the table alive by locking it or anything else?

again, no, it is not possible to guarantee that a global temp table will stay in place.

instead,create a permanent table, and add a job to delete/drop the table after 120 minutes, or have the application check a date that exists inside the now-permanent table, so the data is not used if it is over 120 minutes old.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 100942 More actions · Answer 6

Note that you can use a "real" table name in tempdb; that is, a table name that does not begin with # or ##. The table will then remain until you drop it or tempdb is recreated.

You could also have a process that periodically dropped any non-temp table in tempdb that was created more than 120 minutes ago.

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

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

And if you create a permanent table, it makes sense to create it in the database it is used, not in tempdb.

rajarshi_ghosh_05 Old Hand Points: 348 More actions · Answer 8

Great idea!!!:w00t: This should works for me!! i just tried a little bit testing on this and it works!! .... Will post u the details after i actually implemented it..

Thanks again.... 🙂

rajarshi_ghosh_05 Old Hand Points: 348 More actions · Answer 9

Lynn Pettis (3/29/2013)
And if you create a permanent table, it makes sense to create it in the database it is used, not in tempdb.

That's true but there are several process that needs to be followed prior to add anything in db design... so as long as the table created by sp and destroyed by DB itself... i guess we are ok...

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

rajarshi_ghosh_05 (3/29/2013)
Lynn Pettis (3/29/2013)
And if you create a permanent table, it makes sense to create it in the database it is used, not in tempdb.
That's true but there are several process that needs to be followed prior to add anything in db design... so as long as the table created by sp and destroyed by DB itself... i guess we are ok...

Unless losing the table in tempdb due to an unexpected server crash causes you a problem. If it is in the current database, you can still recover the data if needed. If it is in tempdb, say bye bye data.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 11

The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.

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

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

ScottPletcher (3/29/2013)
The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.

Really? Last I heard all updates, deletes, inserts were all still logged to the transaction log and tempdb has a transaction log.

If the data needs to survive an unexpected server restart or crash, say bye bye to ANY data you had written to ANY tables temporary or permanent that exist in tempdb.

Just saying.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Lynn Pettis (3/29/2013)
ScottPletcher (3/29/2013)
The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.
Really? Last I heard all updates, deletes, inserts were all still logged to the transaction log and tempdb has a transaction log.
If the data needs to survive an unexpected server restart or crash, say bye bye to ANY data you had written to ANY tables temporary or permanent that exist in tempdb.
Just saying.

I think one way to see this is that tempdb does not need to be backed up. Since you don't recover/backup tempdb, it is less overhead.

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

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

SQLRNNR (3/29/2013)
Lynn Pettis (3/29/2013)
ScottPletcher (3/29/2013)
The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.
Really? Last I heard all updates, deletes, inserts were all still logged to the transaction log and tempdb has a transaction log.
If the data needs to survive an unexpected server restart or crash, say bye bye to ANY data you had written to ANY tables temporary or permanent that exist in tempdb.
Just saying.
I think one way to see this is that tempdb does not need to be backed up. Since you don't recover/backup tempdb, it is less overhead.

You can't backup tempdb, you get the following error:

Msg 3147, Level 16, State 3, Line 1

Backup and restore operations are not allowed on database tempdb.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

I just have a real problem with using tempdb in the manner suggested. And I have a problem with SQL Server "knowing" that since it doesn't have to recover data that there are less resources used when writing data to tables in tempdb. I mean, really?