Azure SQL: The database ‘tempdb’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. (Microsoft SQL Server, Error: 40544)

On one of the projects I’m involved in, whatever we wanted to do with our Azure SQL DB (V12), we got this error:

The database ‘tempdb’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. (Microsoft SQL Server, Error: 40544)

As there is not much to do in such situation we asked Microsoft Azure Support for assistance and this is their reply (saved here for further reference):

If you experience the TempDB full issue again, then please leverage the DMVs below and look for active transactions which are taking maximum TempDB space. For a long term fix, I would recommend tuning those queries to use less temp space and if the issue is being caused by multiple queries running concurrently then serialize the workload to not consume all available TempDB space. Here are the queries that will give you insight into which queries are causing this:

Query to get Used and Free Space in TempDB
Note: This can be misleading as it doesn’t include free space up to max size

SELECT
SUM(allocated_extent_page_count) AS [used pages],
(SUM(allocated_extent_page_count)*1.0/128) AS [used space in MB],
SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Retrieving information about which clients (connected to current database) are using TempDB with open transactions

SELECT top 5 a.session_id, a.transaction_id, a.transaction_sequence_num, a.elapsed_time_seconds,
b.program_name, b.open_tran, b.status
FROM tempdb.sys.dm_tran_active_snapshot_database_transactions a
join sys.sysprocesses b
on a.session_id = b.spid
ORDER BY elapsed_time_seconds DESC

Retrieving information about which active tasks (in current database) are using TempDB

SELECT es.host_name , es.login_name , es.program_name,
st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME, st.objectid as ModuleObjectId,
SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE er.statement_end_offset
END - er.statement_start_offset)/2) as Query_Text,
tsu.session_id ,tsu.request_id, tsu.exec_context_id,
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time, er.reads,er.writes,
er.logical_reads, er.granted_query_memory
FROM tempdb.sys.dm_db_task_space_usage tsu
inner join sys.dm_exec_requests er ON ( tsu.session_id = er.session_id and tsu.request_id = er.request_id)
inner join sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id )
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC

Find tempDB data size

SELECT SUBSTRING(st.text, er.statement_start_offset/2 + 1,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),st.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset)/2) as Query_Text,tsu.session_id ,tsu.request_id,
tsu.exec_context_id,
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time,
er.cpu_time, er.total_elapsed_time, er.reads,er.writes,
er.logical_reads, er.granted_query_memory,es.host_name , es.login_name , es.program_name
FROM sys.dm_db_task_space_usage tsu INNER JOIN sys.dm_exec_requests er
ON ( tsu.session_id = er.session_id AND tsu.request_id = er.request_id) INNER JOIN sys.dm_exec_sessions es
ON ( tsu.session_id = es.session_id ) CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+ (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC

Checking if tempDB has an active transaction

Examine log_reuse_wait_desc from sys.databases if you see ACTIVE_TRANSACTION this could mean there is a long running active transaction in tempdb that is blocking log flush =>

Check for active transaction on tempDB

select log_reuse_wait_desc, * from sys.databases where name = 'tempdb'

select d.name, d.log_reuse_wait_desc, t.* from sys.dm_tran_locks t inner join sys.databases d on t.resource_database_id = d.database_id

Few more docs about optimizing temp DB –
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database
https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms190768(v=sql.105).aspx

Additionally, the higher the pricing tier, your database would have more tempdb space as well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s