I've got some rogue temporary tables being generated which is starting to cause disk space issues. The database creating these tables is fairly large and I'm unsure what stored procedure/function etc is causing them. At the moment the tempdb is around 50 gig and only restarting the service clears it down (something we don't want to continually do).
Using SQL Server 2008 R2 SP1
The temp tables are all named in a similar fashion and are located in System Databases> tempdb> Temporary Tables:
dbo.#0519C6AF
dbo.#1273C1CD
dbo.#2A4B4B5E
There are about 12 of them at the moment – all created in the 9 days since the last service restart. So far I’ve been looking for stored procedures that either create temp tables (although I don’t think this is the issue), use cursors, use transactions that could be left uncommitted.
Does anyone know what could cause the creation of temporary tables named similarly to the ones above?
-
1I think that those are the tables for table variables (so look for the use of those instead)Damien_The_Unbeliever– Damien_The_Unbeliever2015年06月09日 14:18:10 +00:00Commented Jun 9, 2015 at 14:18
-
See if the default trace provides insight sqlperformance.com/2014/05/t-sql-queries/… I have multiple tables in that format from SQL Server agent jobsAlex K.– Alex K.2015年06月09日 14:18:59 +00:00Commented Jun 9, 2015 at 14:18
-
@Damien_The_Unbeliever - Quick test confirmed that the tables created when using table variables do look very similar (i.e. dbo.#B4ABA53D) so that helps narrow down the search - thanksTikeb– Tikeb2015年06月09日 14:30:45 +00:00Commented Jun 9, 2015 at 14:30
-
@Rahul - what do you doubt? Temp tables in tempdb should always have a name with a lot of underscores, whereas table variables tend to have names composed of 8 hex-digits - exactly what the OP is showing.Damien_The_Unbeliever– Damien_The_Unbeliever2015年06月09日 14:34:21 +00:00Commented Jun 9, 2015 at 14:34
-
@Damien_The_Unbeliever, removed my comment. Yes absolutely correct.Rahul– Rahul2015年06月09日 14:39:16 +00:00Commented Jun 9, 2015 at 14:39
1 Answer 1
Following what @Damien_The_Unbeliever commented, yes those are Table variables only and if you are still observing them then it's evident that from whichever procedure those got created is still opened (or) not completed yet else those would have already been cleared from TempDB. To do a test try the code snippet and check against System Databases > tempdb > Temporary Tables: you will see that the table variable is exist.
declare @tab table(col1 int);
waitfor delay '8:10';
insert into @tab values(123);
Once you cancel the query, it gets cleared from TempDB.