Friday, February 24, 2012

Global Temp Tables Getting Dropped form Time to Time

Hi all,

I have created several global temp tables to cache some intermediate results ...
However, it seems that after a while those tables will be dropped by SQL Server 2005 automatically (I have not restarted the server and no drop table statement ever executed against those tables). Is this a feature by design? How to make those global temp tables persistence to next service restart?

Thanks,
Ning
....maybe, but either way, you may want to consider using something other than global temp tables. Why don't you just create user tables and clear them out when you need to?|||

This is by design. ##tmp is dropped if the service is restarted and when there is no one else connects to it.

"Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended."

|||

Global temp tables are dropped after the last connection to the server closes. It is by design. As long as a connection is open, the tables persist.

If you need permenance of a table, then don't use 'Temp' tables -they are temporary...

No comments:

Post a Comment