Sql-server – SQL Server tempdb rogue temporary tables

sql serversql-server-2008-r2

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?

Best Answer

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.

Related Solutions

Sql-server – Deadlocks From Locks on Same Temporary Tables In Different Processes

This exact issue was just announced on Deadlocks occur when you execute a stored procedure to alter a temporary table if lock partitioning is enabled in SQL Server 2008 R2. It is linked from Cumulative update package 4 for SQL Server 2008 R2 SP2.

It finally pays off to read SQL Server fix descriptions.

SQL Server Trigger – Create Variable Table for Service Broker

You could send the xml data in predefined chunks by using something like the trigger in the following setup.

Create the testbed in tempdb:

USE tempdb;

IF EXISTS (SELECT 1 FROM sys.triggers t WHERE t.name = 'TriggerTest_Chunked')
DROP TRIGGER dbo.TriggerTest_Chunked;


IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTest')
DROP TABLE dbo.TriggerTest;

CREATE TABLE dbo.TriggerTest
(
    ID INT NOT NULL
);

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTestTarget')
DROP TABLE dbo.TriggerTestTarget;

CREATE TABLE dbo.TriggerTestTarget
(
    OperationType INT NOT NULL
    , DEETS XML NOT NULL
);

The trigger:

CREATE TRIGGER dbo.TriggerTest_Chunked
ON dbo.TriggerTest
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @RowStart INT;
    DECLARE @RowEnd INT;
    DECLARE @RowCount INT;
    DECLARE @BatchSize INT;
    SET @BatchSize = 1000;

    SET @RowStart = 0;

    SELECT @RowCount = COUNT(1) 
    FROM inserted;

    IF @RowCount > @BatchSize 
        SET @RowEnd = @BatchSize
    ELSE
        SET @RowEnd = @RowCount;

    WHILE @RowEnd <= @RowCount AND (@RowStart < @RowEnd)
    BEGIN
        INSERT INTO dbo.TriggerTestTarget (OperationType, DEETS)
        SELECT 1, (
            SELECT ID
            FROM (
                    SELECT *
                        , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    FROM inserted
                ) i_rn
            WHERE i_rn.rn > @RowStart
                AND i_rn.rn <= @RowEnd
            FOR XML PATH('')
        );

        SET @RowStart = @RowStart + @BatchSize;
        IF @RowEnd + @BatchSize > @RowCount
            SET @BatchSize = @RowCount - @RowEnd;
        SET @RowEnd = @RowEnd + @BatchSize;
    END

    SET @RowStart = 0;

    SELECT @RowCount = COUNT(1) 
    FROM deleted;

    IF @RowCount > @BatchSize 
        SET @RowEnd = @BatchSize
    ELSE
        SET @RowEnd = @RowCount;

    WHILE @RowEnd <= @RowCount AND (@RowStart < @RowEnd)
    BEGIN
        INSERT INTO dbo.TriggerTestTarget (OperationType, DEETS)
        SELECT 2, (
            SELECT ID
            FROM (
                    SELECT *
                        , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    FROM deleted
                ) i_rn
            WHERE i_rn.rn > @RowStart
                AND i_rn.rn <= @RowEnd
            FOR XML PATH('')
        );

        SET @RowStart = @RowStart + @BatchSize;
        IF @RowEnd + @BatchSize > @RowCount
            SET @BatchSize = @RowCount - @RowEnd;
        SET @RowEnd = @RowEnd + @BatchSize;
    END
END
GO

Insert some test data to see what the trigger does:

INSERT INTO dbo.TriggerTest (ID)
SELECT ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id) 
FROM sys.objects o1
    , sys.objects o2;

Show results:

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

DELETE TOP(100)
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

Best Answer

Related Solutions

Sql-server – Deadlocks From Locks on Same Temporary Tables In Different Processes

SQL Server Trigger – Create Variable Table for Service Broker

Related Question