Azure Synapse Error : Could not allocate a new page for database ‘tempdb’ because of insufficient disk space
While creating NonClustered Index in synapse dedicated SQL pool, getting the below error:
Could not allocate a new page for database ‘tempdb’ because of insufficient disk space in filegroup ‘PRIMARY’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. The statement has been terminated.
As per the error, tempDB capacity limit for dedicated SQL pool is reached the maximum limit
The most common cause for temp DB to run out of space is due to not enough resources allocated to the query causing data to spill to TempDB.
As per the Microsoft documentation for every DW100c, will get 399GB of temp DB size.
TempDB space is allocated per service level. Scaling your dedicated SQL pool to a higher DWU setting allocates more TempDB space.
Here is the troubleshooting guide for the most common dedicated SQL pool issues.
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-troubleshoot
See the below Microsoft recommendations for when to scale out SQL pools.
Before performing a heavy data loading or transformation operation, scale-out to make the data available more quickly.
During peak business hours, scale out to accommodate larger concurrent queries.
For tempDB auto growth, We can’t change this setting in Azure Synapse SQL pools.
We can use the below query to monitor the temp DB in Synapse.
PRINT ‘Info: Creating the ”microsoft.vw_sql_requests” view’;
GO
CREATE VIEW microsoft.vw_sql_requests
AS
(
SELECT
sr.request_id,
sr.step_index,
(CASE WHEN (sr.distribution_id = -1 ) THEN (SELECT pdw_node_id FROM sys.dm_pdw_nodes WHERE type = ‘CONTROL’) ELSE d.pdw_node_id END) AS pdw_node_id,
sr.distribution_id,
sr.status,
sr.error_id,
sr.start_time,
sr.end_time,
sr.total_elapsed_time,
sr.row_count,
sr.spid,
sr.command
FROM
sys.pdw_distributions AS d
RIGHT JOIN sys.dm_pdw_sql_requests AS sr ON d.distribution_id = sr.distribution_id
)
GO
— Monitor tempdb
SELECT
sr.request_id,
ssu.session_id,
ssu.pdw_node_id,
sr.command,
sr.total_elapsed_time,
exs.login_name AS ‘LoginName’,
DB_NAME(ssu.database_id) AS ‘DatabaseName’,
(es.memory_usage * 8) AS ‘MemoryUsage (in KB)’,
(ssu.user_objects_alloc_page_count * 8) AS ‘Space Allocated For User Objects (in KB)’,
(ssu.user_objects_dealloc_page_count * 8) AS ‘Space Deallocated For User Objects (in KB)’,
(ssu.internal_objects_alloc_page_count * 8) AS ‘Space Allocated For Internal Objects (in KB)’,
(ssu.internal_objects_dealloc_page_count * 8) AS ‘Space Deallocated For Internal Objects (in KB)’,
CASE es.is_user_process
WHEN 1 THEN ‘User Session’
WHEN 0 THEN ‘System Session’
END AS ‘SessionType’,
es.row_count AS ‘RowCount’
FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu
INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id
INNER JOIN sys.dm_pdw_nodes_exec_connections AS er ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id
INNER JOIN microsoft.vw_sql_requests AS sr ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
LEFT JOIN sys.dm_pdw_exec_requests exr on exr.request_id = sr.request_id
LEFT JOIN sys.dm_pdw_exec_sessions exs on exr.session_id = exs.session_id
WHERE DB_NAME(ssu.database_id) = ‘tempdb’
AND es.session_id <> @@SPID
AND es.login_name <> ‘sa’
ORDER BY sr.request_id;
Reference document: https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor.md