database 'tempdb' because the 'PRIMARY' filegroup is full.

Question

SQL Error Info: SrvrMsgState: 2, SrvrSeverity: 17, Error <1>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Could not allocate space for object '' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files,

Answer

If you are receiving this error on a SQL Server (on-premises or Azure VM) instance, please make sure there is enough space where Tempb is allocated and make sure Tempdb file can autogrow.

Please make sure you are dropping temporary tables at the end of stored procedures that create them. Use batching if possible. Monitor what queries are creating big hash tables and tune those queries. Take care of sorting operations on queries that are spilling data to TempDB.

Answer

Hello @Ande Venkatesham ,

I see you added the "Synapse analytics" earlier. If this is an issue with tempDB on the dedicated SQL pool, please follow the below.

1) Optimize queries so that they consume less TEMPDB space. If necessary, consider breaking the workload into more queries so that each batch of execution fits into TEMPDB.

2) Consider increasing the SQL Pool service level while you optimize the queries. For every DW100c configured, 399 GB of TEMPDB space is allocated.

3) Monitor TEMPDB usage to understand which queries are consuming TEMPDB space using the below script.

 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

I hope this helps. If you have any further questions, please let us know.

database 'tempdb' because the 'PRIMARY' filegroup is full.

2 answers