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?