August 7, 2007 at 9:56 am
Hi,
We have a large database that we have recently introduced table variables to!
Whats happening is that were getting loads of temp tables generated in tempdb - they're all called something like dbo.#034FD33 and seem to be generating everytime a large table variable is run
Everywhere i've looked says that table variables clean up after themselves - does anyone have any ideas why this is happening?
It looks like these tables are causing tempdb to grow - but its mostly unallocated space - so at the moment its size: 18877.00 MB -> unallocated: 18852.05 MB but growing steadily.
Theres 163 of these tables at the moment - when we restart the service everything goes back - and then the dbo.#34DE587 tables start appearing!!!
Any help greatly appreciated!!
Cheers,
Andy
August 7, 2007 at 10:02 am
Table variables do harden to disk in tempDB if they get over a certain size.
They also get dropped after the spid is done executing.
if your TempDB is growing, but when you look at it most of the space is free space, it simply means that at one point, tempdb needed the space - and grew. Temp DB will not shrink the file back down once done. Rather leave it.
As for why you see the names after the execution - I am not sure.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 7, 2007 at 10:08 am
I recommend changing a one or two of the stored procedures to use #temp tables instead of table variables. Use: if exist, drop the temp table in two places as a best practice:
1) before the creation of the temp table
2) before the stored procedure returns/terminates
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpTableName')) DROP TABLE #tmpTableName
replacing #tmpTableName with your procedures name.
If the sample procedures still perform well and you can determine that clean up is occuring, change all of the procedures from table variables to #temp tables.
[font="Arial"]Clifton G. Collins III[/font]
August 7, 2007 at 10:14 am
That does solve the problem - i was just hoping to keep the table variables as i thought they were supposed to be a step forward.
I dont suppose anyone knows a way of forcing sql to clean up after its table variables?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply