为什么没有从 SQL Server 中的 tempdb 中删除临时表? [英] Why are temporary tables not removed from tempdb in SQL Server?

问题描述

我创建了一个带有 7 个临时表的存储过程，每个临时表在他们自己的工作结束时被删除.

I have created one stored procedure with 7 temporary tables and each temp table is dropped at the end of their own work.

我从一个 Web 服务和我们用于不同实例的相同 Web 服务调用 SP.

I am calling the SP from one web service and same web service we are used for different instance.

我已经强行删除了每个临时表，但是当 SP 执行时，它不会删除位于tempdb/临时表"中的任何临时表.而且，当我打开应用程序的新实例并尝试执行相同的 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同时执行时它会锁定表它会锁定表并且我的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.

所以我想在操作结束时删除我的临时表.请帮忙.

So I want to drop my temporary tables at the end of my operation. please help.

推荐答案

我不能告诉你为什么会发生这种情况，但我之前也处理过它.尝试在 SP 的开头或结尾清理表格或使用表格变量.

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.

IF object_id('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName

这篇关于为什么没有从 SQL Server 中的 tempdb 中删除临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！