The tempdb is a system database that holds the temporary user objects and internal objects.
Azure SQL DB
In Azure SQL DB tempdb is not visible under System Databases after connecting with SSMS but it is still important in order to avoid workload problems due to the limitations that it has. This limitations are different with the service level objective that we choose between:
For DTU purchase model the limits will be this and for vCore we can find the TempDB max data size (GB) under each service objective here.
Azure Managed Instance
In Managed Instance tempdb is visible and it is split in 12 data files and 1 log file:
All system databases and user databases are counted as used storage size as compared to the maximum storage size of the instance.
To check the values for this sizes we can run this query:
select top 1
used_storage_gb = storage_space_used_mb/1024,
max_storage_size_gb = reserved_storage_mb/1024
from sys.server_resource_stats order by start_time desc
For Managed Instance we have two service tiers: General Purpose and Business Critical for which the max tempdb size is:
| General Purpose | Business Critical |
| Limited to 24 GB/vCore (96 - 1,920 GB) and currently available instance storage size. Add more vCores to get more TempDB space. Log file size is limited to 120 GB. |
Up to currently available instance storage size. |
Issues due to tempdb usage
When executing a huge transaction with million of rows and high tempdb usage we might get the following error message:
Msg 40197, Level: 20, State: 1, Procedure storedProcedureName, the service has encountered an error processing your request. Please try again. Error code 1104
This does not give too much information but by executing the following:
select * from sys.messages where message_id = 1104
we will get more context on it:
TEMPDB ran out of space during spilling. Create space by dropping objects and/or rewrite the query to consume fewer rows. If the issue still persists, consider upgrading to a higher service level objective.
Some other errors that we might get:
- 3958 Transaction aborted when accessing versioned row. Requested versioned row was not found. Your tempdb is probably out of space. Please refer to BOL on how to configure tempdb for versioning.
- 3959 Version store is full. New versions could not be added. A transaction that needs to access the version store may be rolled back. Please refer to BOL on how to configure tempdb for versioning.
- 3966 Transaction is rolled back when accessing version store. It was marked as a victim because it may need the row versions that have already been removed to make space in tempdb. Not enough disk space allocated for tempdb, or transaction running for too long and may potentially need the version that has been removed to make space in the version store. Allocate more space for tempdb, or make transactions shorter.
- log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION' and the holdup lsn is (196:136:33).
To investigate this ones this queries should be useful:
dbcc sqlperf(logspace)
select name, log_reuse_wait_desc, * from sys.databases
SELECT * FROM tempdb.sys.dm_db_file_space_usage
GO
Select * from sys.dm_tran_version_store_space_usage
GO
SELECT * FROM sys.dm_exec_requests where open_transaction_count > 0
go
ACTIVE_TRANSACTION:
SQL Server will return a log_reuse_wait_desc value of ACTIVE_ TRANSACTION if it runs out of virtual log files because of an open transaction. Open transactions prevent virtual log file reuse, because the information in the log records for that transaction might be required to execute a rollback operation.
To prevent this log reuse wait type, make sure you design you transactions to be as short lived as possible and never require end user interaction while a transaction is open.
To reduce tempdb utilization we will need to look at the common tempdb usage areas which are:
- Temp tables
- Table variables
- Table-valued parameters
- Version store usage (associated with long running transactions)
- Queries that have query plans that use sorts, hash joins, and spools
To identify top queries that are using temporary tables and table variables we can use this query
To monitor the tempdb utilization the below query can be run with a 15 minute delay (it will print "high tempdb utilization" if the usage exceeds 90%):
DECLARE @size BIGINT
DECLARE @maxsize BIGINT
DECLARE @pctused BIGINT
DECLARE @unallocated BIGINT
DECLARE @used BIGINT
SELECT @size = Sum (size),
@maxsize = Sum (max_size)
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS'
SELECT @unallocated = Sum (unallocated_extent_page_count)
FROM tempdb.sys.dm_db_file_space_usage
SELECT @used = @size - @unallocated
SELECT @pctused = Ceiling (( @used * 100 ) / @maxsize)
--select @used, @pctused
IF ( @pctUsed > 90 )
BEGIN
PRINT Cast (Getutcdate() AS NVARCHAR(50))
+ N': high tempdb utilization'
END
GO
Other useful links