0

Possible Duplicate:
Why are temporary tables not removed from tempdb in SQL Server?

We have created Temporary tables in a stored procedure and while running the stored procedure the Temparory table is created in the TempDB.

In my SP I have written code to drop that temporary table after completion of all operations manually.

But it is still there in the TempDB and not dropped. But when I restarted the SQL connection then the table had been dropped. I don't understand how this type of scenario happen in my DB.

Why are my temporary tables not dropped after completion of their scope?

Can anyone help me out this problem?

Community
  • 1
  • 1
Brijesh Patel
  • 2,901
  • 15
  • 50
  • 73
  • 1
    You asked the same question a week ago: http://stackoverflow.com/questions/6623846/why-are-temporary-tables-not-removed-from-tempdb-in-sql-server – Mitch Wheat Jul 12 '11 at 08:31
  • Are you executing this in SQL Management Studio? If yes, then that is your problem. SQL Management Studio doesn't close the connection after you're done executing your SP. – Shiroy Jul 12 '11 at 08:32
  • If you try to re-create the temporary table after manually dropping it, does the SQL throw an error about the table already existing? Could it just be an artifact of how the server implements table dropping? IE: has the table actually been dropped successfully, but the entry in tempDB still remains for some reason? – xan Jul 12 '11 at 08:36
  • Show us some code please – gbn Jul 12 '11 at 08:46
  • It would be helpful if you could show some code that will demonstrate the problem. Also, do you get an error or something? – Mikael Eriksson Jul 12 '11 at 08:47
  • Is it related to this one specific server? may be cannot duplicate it on other servers of the same build. – Brijesh Patel Jul 12 '11 at 09:34

1 Answers1

1

I'm not sure why your table is not being dropped, but could you get away with using a table variable (@TableName) instead of a temp table (#TableName)? Then you wouldn't have to worry about dropping it at all, and it would be removed as soon as it goes out of scope...

Fergus Bown
  • 1,666
  • 9
  • 16
  • You're restating a Myth: table variables are held in tempdb; plus then you would have to worry about is performance, because there are no statistics on table vars – Mitch Wheat Jul 12 '11 at 08:35
  • @Mitch - the point about statistics is a good one, that's why I asked whether he could get away with it - on a small number of rows this shouldn't be a problem. I don't think anything else I have said is inaccurate though is it? If it is, I'd be interested to know for my own development! – Fergus Bown Jul 12 '11 at 08:41
  • Fergus: "table variables are held in tempdb" – Mitch Wheat Jul 12 '11 at 08:42
  • Right - but who said they weren't? I certainly didn't. I said they were automatically dropped when they went out of scope, which I believe they are (As stated here http://odetocode.com/code/365.aspx for example) – Fergus Bown Jul 12 '11 at 08:45
  • Oh, and don't forget the gotcha with transactions.... – Mitch Wheat Jul 12 '11 at 08:47