Your process was involved in unresolved deadlock, therefore, the tables were not dropped and blocked the creating table process in tempdb.

This might be the bug in sqlserver 2005.

create proc proc1

as

begin

create table #abcd ( i int)

drop table #abcd

end

go

BEGIN TRAN

go

DECLARE @I INT

SET @I = 1

WHILE @I < 5000

BEGIN

EXEC proc1

SET @I = @I + 1

END

go

Select count(*) from tempdb.sys.tables

go

If you notice the last output, the tempdb now has 5000 temp tables. From a programmer perspective, as soon as a stored procedure finishes, the temp table scope is over and it is destroyed. In SQL2005 we do a deferred drop, but that doesn’t kick in until transaction is over.

Because of the begin tran command, the temp tables are not dropped and hence tempdb locks not released. However, these tables are no longer accessible even to the SPID which created it, but are still present in tempdb.

Saby DBA