TempDB Recovery Model

TempDB always in Simple Recovery Model. TempDB is a temporary workspace and SQL Server does NOT need to retain its transaction log for point-in-time recovery. TempDB stores only temporary and non-persistent data. SQL Server recreates TempDB every restart, so nothing needs recovery. It is extremely write-intensive, so SIMPLE model avoids log growth and improves performance. TempDB cannot be backed up, so Full/Bulk Logged recovery is meaningless. Therefore, SQL Server enforces SIMPLE mode permanently we cannot change it.

Let’s check the recovery model

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'tempdb';

We can’t change it. Let’s try to change it from SIMPLE to Full.

ALTER DATABASE tempdb SET RECOVERY FULL;

Let’s create a temp table and store data into that table

Before loading the data see the size of temp db.

Now inserting the records

use AdventureWorks2019 ;with cte as ( SELECT  a.* FROM sys.objects a CROSS JOIN sys.objects b union all SELECT  a.* FROM sys.objects a CROSS JOIN sys.objects b union all SELECT  a.* FROM sys.objects a CROSS JOIN sys.objects b union all SELECT  a.* FROM sys.objects a CROSS JOIN sys.objects b union all SELECT  a.* FROM sys.objects a CROSS JOIN sys.objects b ) select * into #BigTable from cte

See the records in this table

Now we will see the size of temp DB.

Temp DB size is grown.

After the Server restart the table and DB size clean.

Let’s restart the Server and see.

Showing invalid object

See the size of the Temp DB.

Log shrinks automatically because SIMPLE = auto truncation No log backup required.

We can not take backup of TempDB.