SQL Server 2005 中的临时表不会自动删除 [英] Temp tables in SQL Server 2005 not automatically dropped

问题描述

我正在对一个讨厌的存储过程进行故障排除，并注意到在运行它之后，并且我已经关闭了我的会话，许多临时表仍然留在 tempdb 中.它们的名称如下:

I'm troubleshooting a nasty stored procedure and noticed that after running it, and I have closed my session, lots of temp tables are still left in tempdb. They have names like the following:

#000E262B
#002334C4
#004E1D4D
#00583EEE
#00783A7F
#00832777
#00CD403A
#00E24ED3
#00F75D6C

如果我运行此代码:

if object_id('tempdb..#000E262B') is null   
print 'Does NOT exist!'

我明白了:

Does NOT exist!

如果我这样做:

use tempdb
go
drop TABLE #000E262B

我收到一个错误:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#000E262B', because it does not exist or you do not have permission.

我以系统管理员身份连接到 SQL Server.使用 SP3 64 位.我目前在 tempdb 中有超过 1100 个这样的表，我无法摆脱它们.数据库服务器上没有其他用户.

I am connected to SQL Server as sysadmin. Using SP3 64-bit. I currently have over 1100 of these tables in tempdb, and I can't get rid of them. There are no other users on the database server.

停止和启动 SQL Server 对我来说不是一种选择.

Stopping and starting SQL Server is not an option in my case.

谢谢！

推荐答案

http://www.sqlservercentral.com/Forums/Topic456599-149-1.aspx

如果经常使用临时表或表变量，那么 SQL 不会删除它们，而是截断"它们，留下定义.这样可以节省下次需要时重新创建表的工作.

If temp tables or table variables are frequently used then, instead of dropping them, SQL just 'truncates' them, leaving the definition. It saves the effort of recreating the table next time it's needed.

这篇关于SQL Server 2005 中的临时表不会自动删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！