December 17, 2009 at 10:00 am
BOL says "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".
I took this to mean that if another session began accessing the table after the creating session ended, but before the table was dropped (due to other sessions referencing it), then the table would still be dropped even before the new sesssion had finished, resulting in an error in that session.
eg
Session 1 creates a global temp table.
Session 2 starts a process referencing that table.
Session 1 ends. The table still exists as Session 2 is referencing it.
Session 3 starts a process referencing the table.
Session 2 ends, so the table is dropped.
Session 3 gets a 'table does not exist' error.
In fact, I've just tried it and this is indeed what seems to happen.
Session 1.
create table ##test (id int)
Session 2.
begin tran
insert into ##test values (1)
Close session 1.
Session 2.
select * from ##test
(1 rows affected)
Session 3.
select * from ##test
(does not report an error, but waits because session 2 has the table locked)
Session 2.
commit
Session 3
Invalid object name '##test'.
So "When all active tasks have stopped referencing them" isn't actually correct, it's when all tasks have stopped referencing them that were active at the time that the creating session ended.
Someone will shortly point out the obvious flaw in my example 😉
December 17, 2009 at 10:20 am
Steve Jones - Editor (12/17/2009)
Whether it's dropped on shutdown or start up , how do you know? In between shutdown beginning and startup completing, you don't have any way to access the objects.
I agree that between the shutdown and the restart there is no access to the objects. However, in the shutdown for safety sake SQL Server I though swept the connections/sessions and ends them in an orderly manor before the shutdown is completed. This avoids certain clutter or garbage that could cause problems after the restart.
And again I know that it is in fact a mute point but I thought I read that somewhere along the line.
M.
PS I missed the answer, so you miss a few along the line. That is the way life works...
Not all gray hairs are Dinosaurs!
December 17, 2009 at 10:59 am
i selected the same and i want my point too 😀
December 17, 2009 at 12:44 pm
vk-kirov (12/17/2009)To those people who answered '1,3,4': is the condition 'all active tasks have stopped referencing them' sufficient for dropping the table? The answer is 'No, it is not'. If the session that created the table still exists, the table remains even if that session is not active. So, if #2 is intended to be marked as incorrect, #3 should be marked as incorrect too.
(By the way, I also answered '1,3,4' 🙂 )
I think that's a question of terminology. I consider an open session an active task.
December 17, 2009 at 2:00 pm
How can you claim that the temp table is dropped during a restart? It may or may not be dropped during an orderly shutdown, but on startup a fresh tempdb database is created and the global temp table is not there to be dropped. If the restart takes place after a power failure or some other catastrophe, there was no orderly shutdown and the table was not dropped.
It's a real strech to claim that the word "restart" includes the shutdown. With a serious hardware crash where you worry it may take days to rebuild the server before you get it running again, can you legitimately report that the server is restarting because the "restart" period began when the crash occurred? If you retire a server that might be restarted in the future if the need arises, is it shut down or is in some uncertain quantum shut down/restarting state like Schroedinger's cat?
Even claiming that the table is dropped during shutdown is a stretch. Is there code in the shutdown process that specifically drops global temp tables, or are they dropped as a side effect of closing connections?
December 17, 2009 at 2:20 pm
Scott Coleman (12/17/2009)
Even claiming that the table is dropped during shutdown is a stretch. Is there code in the shutdown process that specifically drops global temp tables, or are they dropped as a side effect of closing connections?
Scott
I had thought they are dropped as a side effect of closing connections not as an independent action.
Not all gray hairs are Dinosaurs!
December 17, 2009 at 2:27 pm
How can there be a temporary table on start up of an instance? When the instance was shutdown the tempory table would have been eliminated. On restart you get a new tempdb so nothing is in it. There was to temp table to clean up so I do not under the on startup logic.
Please explain.
December 17, 2009 at 3:07 pm
I'm also not totally agree with you.
When the instance restarts : true : all connections are closed, and the tempdb is recreated from 0 when the instance starts.
When the session that created it ends : false : That aplies for local temporal tables.
When all active tasks have stopped referencing them : false : That aplies for local temporal tables.
When specifically dropped by name : true : and the drop will be done as soon as there is no more lock on it.
December 17, 2009 at 3:42 pm
wow, you guys are really splitting hairs. A restart includes a shutdown and startup of the database instance. How could you argue it includes anything else? In line with a shutdown, it would close connections, which would drop the table. Irrelevant if it's still on disk somewhere during downtime, it's dropped for all practical purposes. Arguing anything about where it might be is just silly.
global tables are dropped when the session that created them ends. That's important to realize. Try it, create a table in one session (## table), select from it in a 2nd. With the 2nd idle, close session 1. The table is not available in session 2.
If the connection is referencing the table in #2 when session 1 closes, as in a transaction of some sort, the table still exists when session 1 ends. However when session 2 stops it's transaction, the table is dropped.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 17, 2009 at 4:05 pm
Who's splitting hairs? Invoking a restart of a running server causes it to first shut down, but that's not the only way a restart occurs.
As I thought I made clear with my earlier examples, in cases where there is significant passage of time between shutdown and restart it would be ridiculous to claim that the "restart" event includes the entire duration. Imagine the server dies catastrophically, and you are up to your elbows in a motherboard replacement. Would you seriously tell someone that the server is currently restarting? Or does the word "restart" not apply until the server is back up and the SQL Server service is started successfully?
Also, in the case of a power failure, crash, or other sudden failure, the table never gets dropped. Building a new tempd during restart is not the same as dropping a table.
December 17, 2009 at 4:32 pm
Thank you for your explanation. That was a misunderstanding about the global tables theory. Usually I used them into sql_execution or EXEC sentences which creates another session. I never use a fixed ##temp name in my procedures, because that could make crash concurrent processes (just in case that dropping process wasn't successful). Anyway thank you for your valuable explanation.
December 18, 2009 at 12:19 am
sknox (12/17/2009)
I think that's a question of terminology. I consider an open session an active task.
Ok, let's see BOL about active tasks 🙂
What is a task? http://msdn.microsoft.com/en-us/library/ms189267.aspx
A task represents a unit of work that is scheduled by SQL Server. A batch can map to one or more tasks. For example, a parallel query will be executed by multiple tasks.
Can we see a list of active tasks? Yes, there's a system view for this: sys.dm_os_tasks, http://msdn.microsoft.com/en-us/library/ms174963.aspx
Returns one row for each task that is active in the instance of SQL Server.
There's a column 'session_id' in this view: 'ID of the session that is associated with the task'.
Here is a test:
Session 1:
select @@spid -- the result is 98
Session 2:
select @@spid -- the result is 104
select distinct session_id
from sys.dm_os_tasks
order by session_id -- the result is NULL, 1, 2, 3, 4, 5, 8, 9, 10, 11, 12, 20, 24, 61, 64, 82, 92, 104
No 98 amongst these values. Therefore the session #98 is open but have no active tasks.
Here is the total number of active tasks:
select count(*) from sys.dm_os_tasks -- the result is 62
Here is the total number of open sessions:
exec sp_who -- 267 row(s) affected
The values 62 and 267 are so different 🙂
December 18, 2009 at 2:27 am
Steve Jones - Editor (12/17/2009)
wow, you guys are really splitting hairs. A restart includes a shutdown and startup of the database instance. How could you argue it includes anything else? In line with a shutdown, it would close connections, which would drop the table. Irrelevant if it's still on disk somewhere during downtime, it's dropped for all practical purposes. Arguing anything about where it might be is just silly.
Glad it's not just me who was thinking that 🙂
And I got that option wrong too, due to not engaging brain before clicking.
No-one seems to want to comment about my earlier reply though, maybe I'm the only one who found the behaviour interesting :crying:
December 18, 2009 at 3:43 am
Toreador (12/18/2009)
No-one seems to want to comment about my earlier reply though, maybe I'm the only one who found the behaviour interesting :crying:
I shall try to comment, this seems to be interesting 🙂
Session 1.
create table ##test (id int)
This session has SPID = 77, the table has object_id = 1007829037.
Session 2.
begin tran
insert into ##test values (1)
This session has SPID = 97.
Close session 1.
Query window closes, but the session remains!
exec sp_who2 77 -- the session has 'SUSPENDED' status and is blocked by session #2.
Let's see what #1 is waiting for:
select * from sys.dm_tran_locks where request_session_id = 77
The result contains the following row:
resource_type = OBJECT, resource_database_id = 2 (tempdb), resource_associated_entity_id = 1007829037 (##test), request_mode = Sch-M (DROP TABLE in this case), request_status = WAIT
So #1 wants to drop the table.
Session 3.
select * from ##test
(does not report an error, but waits because session 2 has the table locked)
#3 waits for the table, but #1 also waits and has higher priority than #3.
Session 2.
commit
Locks released, #1 captures and drops the table.
Session 3
Invalid object name '##test'.
Because the table has been dropped by #1 🙂
December 21, 2009 at 1:09 am
Got beaten but this was an eye opener for me. even though I went with #2 and #3 I am opting to do another test to show my argument.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply