Delete data from tempdb for Synapse dedicated SQL Pool

Question

Hi Team,

We are getting an error while inserting data into temp table in Synapse.

SqlNativeBufferReader.ReadBuffer, error in OdbcReadBuffer: SqlState: 42000, NativeError: 1101, 'Error calling: SQLFetch(this->GetHstmt()), SQL return code: -1 | SQL Error Info: SrvrMsgState: 1, SrvrSeverity: 17, Error <1>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'.

Can you please help us on how to delete data from tempdb in Synapse dedicated sql pool.

Answer 1

Hello @Shravya Pusa ,
Welcome to the MS Q&A platform.
Per the error message, you are running out of space on the tempdb. Please follow the below steps

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) Please make sure to drop temporary tables at the end of stored procedures that create them

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

4) 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 me know.