GLOBAL_TEMPORARY_TABLE_AUTO_DROP: Keeping ## Tables Alive on Purpose

Create a global temporary table, then disconnect before its next reader arrives. GLOBAL_TEMPORARY_TABLE_AUTO_DROP changes that lifetime rule. The setting also creates a cleanup responsibility that deserves more attention than its long name.

Know Which Version and Scope Apply

SQL Server 2025 supports this database scoped configuration, but the feature predates that release. It is available starting with SQL Server 2019 and in Azure SQL Database. Azure SQL Managed Instance also supports the option.

On SQL Server and Managed Instance, configure it in tempdb. Setting the option in another database does not control global temporary-table cleanup. Although the syntax says database scoped, SQL Server global temporary tables remain shared across the instance.

Azure SQL Database uses a different boundary. Configure GLOBAL_TEMPORARY_TABLE_AUTO_DROP in the user database containing the workload. Global temporary tables are shared among connections to that database, rather than connections to every database on its logical server.

I check the engine version and current database before changing lifetime settings. I also write down the original value before testing. A copied configuration statement becomes dangerous when its scope is broader than the person running it expects.

Read the GLOBAL_TEMPORARY_TABLE_AUTO_DROP Default Precisely

ON is the default. A global temporary table becomes eligible for automatic removal when its creating session ends. SQL Server also waits for currently referencing tasks to stop using the table.

The important detail is the lifetime of the referencing statement. Another open connection does not keep the table alive merely because it queried the table earlier. The association lasts for the statement actively referencing the object when the creator disconnects.

Consequently, an idle consumer session provides no guarantee for its next SELECT. A completed SELECT does not reserve the object for later work. Applications need explicit coordination if the producer can disconnect before consumers finish.

A global table uses two number signs, while a local temporary table uses one. That extra character changes visibility and cleanup assumptions considerably. It does not add a private mailbox for the intended reader.

Inspect GLOBAL_TEMPORARY_TABLE_AUTO_DROP before Changing It

Run the following SQL Server inspection in a test instance. It reads the current configuration without changing it. The named row should exist on a supported engine version.

USE tempdb;
GO
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name = N'GLOBAL_TEMPORARY_TABLE_AUTO_DROP';
SELECT SERVERPROPERTY('ProductVersion') AS EngineVersion,
       DB_NAME() AS CurrentDatabase;

For Azure SQL Database, connect directly to the intended user database and run the configuration query there. Do not copy the USE tempdb instruction into that workflow. Verify the database name before making the change.

An administrator needs the appropriate database configuration permission, such as ALTER ANY DATABASE SCOPED CONFIGURATION. Table creation permission and configuration permission are different responsibilities. Permission to use a temporary table does not authorize an instance-wide policy change.

Use an isolated SQL Server test instance for the next demonstration. Changing tempdb's setting changes the behavior of other global temporary tables too. Schedule a coordinated experiment if shared test workloads depend on the existing behavior.

Compare Two Lifetime Settings Across Connections

First record the original value from the inspection query. The next block deliberately changes lifetime behavior in SQL Server tempdb. It is demonstration setup, not a recommended production default.

USE tempdb;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET GLOBAL_TEMPORARY_TABLE_AUTO_DROP = OFF;
GO

In connection A, create the following uniquely named sample object. Confirm that the name is unused before running CREATE. A collision is a reason to choose another sample name, not to delete someone else's table.

IF OBJECT_ID(N'tempdb.dbo.##LifetimeDemo') IS NOT NULL
    THROW 51000, 'Choose another unused demonstration table name.', 1;
CREATE TABLE ##LifetimeDemo
(
    ItemId int NOT NULL PRIMARY KEY,
    Payload varchar(30) NOT NULL
);
INSERT ##LifetimeDemo VALUES (1, 'sample payload');
SELECT @@SPID AS CreatingSession;

Close connection A after the insert completes. In connection B, the following lookup demonstrates the intended OFF behavior. Dynamic execution defers compilation of the table reference until the existence check succeeds.

IF OBJECT_ID(N'tempdb.dbo.##LifetimeDemo') IS NOT NULL
    EXEC sys.sp_executesql
        N'SELECT ItemId, Payload FROM ##LifetimeDemo;';
ELSE
    SELECT N'The demonstration table is absent.' AS Observation;

With OFF, creator disconnection alone does not automatically remove this table. Explicit DROP TABLE or a Database Engine restart ends its lifetime. Tempdb storage still has no durability guarantee, so this is not a replacement for a permanent staging table.

To compare ON, clean up the first object, change the setting, and repeat creation using connection A. After A disconnects, check from an idle B connection. Do not claim an exact deletion timing from a separate, uncontrolled consumer statement.

Assign Cleanup without Assuming Private Ownership

The creator can explicitly drop the global table while connected. Global temporary objects are accessible across their supported scope, and another session can also issue DROP TABLE. Treat their names and contents as shared resources rather than protected application ownership.

OFF does not reserve cleanup for the original session. An administrative cleanup connection can remove the table after the creator leaves. Test the intended cleanup identity and its behavior during the isolated experiment, including concurrent readers.

A DROP needs the appropriate schema-modification lock. An active statement can therefore delay cleanup through blocking. A cleanup timeout should produce a visible operational result, rather than silently leaving the application convinced its staging object disappeared.

Use the following cleanup only for the sample object you created. Existence checks do not provide a complete concurrency protocol. Another connection can change the object between checking and using it.

DROP TABLE IF EXISTS ##LifetimeDemo;
GO
USE tempdb;
GO
-- Restore ON only when ON was the recorded original value.
ALTER DATABASE SCOPED CONFIGURATION
SET GLOBAL_TEMPORARY_TABLE_AUTO_DROP = ON;
GO

If the recorded original value was OFF, restore OFF instead. Keep restoration separate from assumptions about the factory default. Recheck the configuration row after cleanup so the experiment leaves a documented final state.

Choose a Safer Handoff When Needed

A global temporary table can expose intermediate records to unrelated sessions. Shared naming also introduces collisions, accidental deletion, and consumers reading incomplete data. Random names reduce collisions but do not create a reliable security boundary.

I prefer a permanent staging table with a batch identifier when work must survive connection changes. Explicit permissions, status columns, and cleanup dates make its ownership clearer. Local temporary tables remain simpler when every operation uses the same connection.

Does your handoff need persistence, privacy, or only short-lived shared visibility? Those are separate requirements with different designs. GLOBAL_TEMPORARY_TABLE_AUTO_DROP addresses automatic lifetime, not the entire handoff contract.

A table that refuses to leave still needs someone assigned to close the door. Define that responsibility before switching OFF. Include abandoned batches, failures, restarts, and cleanup blocking in the operational plan.

Related reading on this blog: Dropping Temp Table in Stored Procedure: SQL in Sixty Seconds #124 and Regular Table or Temp Table: TempDB Logging Explained.

A longer temporary-table lifetime is not durable storage, it is an explicit cleanup obligation.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.