为什么没有从 SQL Server 中的 tempdb 中删除临时表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6623846/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why are temporary tables not removed from tempdb in SQL Server?
提问by Brijesh Patel
I have created one stored procedure with 7 temporary tables and each temp table is dropped at the end of their own work.
我创建了一个带有 7 个临时表的存储过程，每个临时表在他们自己的工作结束时都被删除。
I am calling the SP from one web service and same web service we are used for different instance.
我从一个 Web 服务和我们用于不同实例的相同 Web 服务调用 SP。
I have dropped every temp table forcefully but when SP executes it will not delete any of the temporary table which are located in "tempdb/Temporary Table". And, when I open new instance of my application and try to execute same SP it will modify same temp tables.
我已经强行删除了每个临时表，但是当 SP 执行时，它不会删除位于“tempdb/Temporary Table”中的任何临时表。而且，当我打开应用程序的新实例并尝试执行相同的 SP 时，它将修改相同的临时表。
This creates problem for me. it will lock the tables when SP execute simultaneously it will lock the table and my sp is not able to produce result and throw exception.
这给我带来了问题。它会在 SP 同时执行时锁定表它会锁定表并且我的 sp 无法产生结果并抛出异常。
So I want to drop my temporary tables at the end of my operation. please help.
所以我想在操作结束时删除我的临时表。请帮忙。
回答by Jeremy Gray
I can't tell you why this is happening, but I have dealt with it before as well. Try cleaning up your tables at the beginning or end of the SP or using table variables.
我不能告诉你为什么会发生这种情况，但我以前也处理过它。尝试在 SP 开始或结束时清理您的表或使用表变量。
IF object_id('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName
回答by Azhar Iqbal
This can occur in case if you have used many Temp tables and you have some Error in between of Your sp and your drop statement could not executed.
So its always best practice to use
如果您使用了许多临时表并且在您的 sp 和 drop 语句之间有一些错误无法执行，则可能会发生这种情况。
所以它始终是使用的最佳实践
IF object_id('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName
in start of SP.
在 SP 的开始。
回答by user2492359
IF object_id('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName
I think this will not work as we know sql server store temp table name with adding some extra character. if exists(select 1 from tempdb.sys.tables where name like '#TableName%') DROP TABLE #TableName
我认为这不会起作用，因为我们知道 sql server 存储临时表名称并添加了一些额外的字符。如果存在（从 tempdb.sys.tables 中选择 1，其中名称类似于 '#TableName%'）DROP TABLE #TableName
回答by WaitForPete
To force dropping of temp tables use
强制删除临时表使用
BEGIN TRY DROP #MyTable END TRY BEGIN CATCH END CATCH
Ugly but effective. Use a separate TRY for each temporary table.
丑陋但有效。对每个临时表使用单独的 TRY。
