2

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?

Improve this question
5
  • 1
    I think that those are the tables for table variables (so look for the use of those instead)
    – Damien_The_Unbeliever
    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 jobs
    – Alex K.
    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 - thanks
    – Tikeb
    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
    Jun 9, 2015 at 14:34
  • @Damien_The_Unbeliever, removed my comment. Yes absolutely correct.
    – Rahul
    Jun 9, 2015 at 14:39

1 Answer 1

Reset to default
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.