Serverless SQL is not clearing the tempdb after querys now it's full and we cannot use the database anymore (bug?)

RickMsBi 86

Hi all,

We are using serverless SQL and have been, for the past year. Currently we have an issue with the tempdb not clearing up. Normal behaviour would be, read data in temptable (I guess) and return the storage after a query completes. It looks like the storage space is not freed up after running querys.

Even selecting the "1" from the smallest of external tables or views results in the following error.

There are currently no open running transactions.

Error 0x27 - Could not allocate tempdb space while transferring data from one distribution to another. See 'https://aka.ms/monitor-temp-db' for assistance.

User's image

Is there any way to clear the temp table, like what was possible in on-premise sql server? Or is it periodically cleared? Right now, it just stays completely maxed out and we can't run any querys apart from query's that do not touch any external data like "select 1 + 1".

Regards,

Rick

BhargavaGunnam-MSFT 27,081 Reputation points Microsoft Employee

2023-08-02T19:50:58.76+00:00
Hello RickMsBi,

The error "Could not allocate tempdb space while transferring data from one distribution to another" is returned when the query execution engine can't process data and transfer it between the nodes executing the query. This error is returned when the resources allocated to the tempdb database are insufficient to run the query.

Serverless SQL pool automatically manages the tempdb. Unlike Azure SQL, users can't clean up the tempDB

Please follow the best practices to reduce the TempDB usage for the queries.

This could include but is not limited to:

Use appropriate data types - Use the smallest data size to accommodate the largest possible value.

Check inferred data types - Schema inference helps you quickly write queries and explore data without knowing file schemas. The cost of this convenience is that inferred data types might be larger than the actual data types.

Filter optimization

Push wildcards to lower levels in the path

Use filename and filepath functions to target specific partitions

Use proper collation to utilize predicate pushdown for character columns

Optimize repeating queries - Use CETAS to enhance query performance and joins

Create and Update statistics frequently for a better cardinality estimation

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool#prepare-files-for-querying

I hope this helps. Please let me know if you have any further questions.
RickMsBi 86 Reputation points

2023-08-02T20:26:43.7233333+00:00

Thanks for your reply. I'm aware of the best practices. The tempdb is full, and azure fails to manage it (clear it up) so something in the backend is not working. I checked for running transactions, and there is none. I would expect space in tempdb to be cleared up when there are no transactions running. Even selecting one row from a 1 byte csv file using a view would fail. So in short, the part "Serverless SQL pool automatically manages the tempdb.", Is not doing it's job. Regards, Rick
RickMsBi 86 Reputation points

2023-08-02T20:57:56.6233333+00:00

In addition, the database doesn't respond anymore, to any query's, even connecting is not possible.
BhargavaGunnam-MSFT 27,081 Reputation points Microsoft Employee

2023-08-02T22:02:23.74+00:00

RickMsBi

SELECT * FROM sys.dm_exec_requests

Using the above query, please check if any other queries are running simultaneously and using up the tempdb space.

If you find any queries that are using up the tempdb space, you can try optimizing them or running them at a different time to reduce the load on the tempdb.
BhargavaGunnam-MSFT 27,081 Reputation points Microsoft Employee

2023-08-02T22:09:30.5633333+00:00

@RickMsBi T

Can you please confirm if you cancel any queries during the STATS creation?

This is one of the reasons for transactions are left active after the query has been canceled, which leads to a constantly growing tempdb log in the serverless sql pool.
RickMsBi 86 Reputation points

2023-08-03T07:24:18.38+00:00

There were some issues with people running powerbi dashboards and having to kill the application because it was unresponsive. How would I recognize these query's, and how do I kill them? If I check the "monitor" part in synapse, I don't see any running (currently I don't see any at all) query's.
RickMsBi 86 Reputation points

2023-08-03T07:41:05.47+00:00

Today we are getting this error if we try to query the serverless database.

CREATE FILE encountered operating system error 39(The disk is full.) while attempting to open or create the physical file 'sbs://./tempdb/b5e7d3b6-3d3d-435d-ab9f-f7541ce54774?type=1'.
RickMsBi 86 Reputation points

2023-08-03T08:06:31.47+00:00

Using SELECT * FROM sys.dm_exec_requests, we have a lot of running tasks from 30-07. I can only kill the user tasks, but there are a lot of other tasks still running and I can't get rid of them, how do we make sure they are killed or give back the space? I think we know the cause, but that doesnt mean we have a solution to the current situation yet.

Regards,

Rick
RickMsBi 86 Reputation points

2023-08-03T08:09:03.8033333+00:00

Here is the file with the current result from dm_exec_requests.dm_exec_requests.txt
BhargavaGunnam-MSFT 27,081 Reputation points Microsoft Employee

2023-08-03T18:47:25.7533333+00:00

Hello RickMsBi,

If there are no active transactions in tempDB then the below two commands can help release the space.

DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ]

DBCC FREEPROCCACHE

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-dropcleanbuffers-transact-sql?view=sql-server-ver16

If there are still active transactions then TempDB space can't be released by either committing or rolling back.

I suspect this could be a bug.

Since you cannot release the space, please log a support ticket.

The support team can work with PG to failover the backend node, which will release the space, and they can provide you with the root cause.

If you don't have any support plan, please let me know so I can provide you a one-time free support ticket to work on this.
RickMsBi 86 Reputation points

2023-08-22T08:25:52.4966667+00:00

It tooks 3 days for the tempDB to be cleared. Though query limit on serverless sql is 30 minutes. So it's definitely an issue, but it didn't happen again afterwards. We're good for now it seems.
BhargavaGunnam-MSFT 27,081 Reputation points Microsoft Employee

2023-08-22T16:46:07.0766667+00:00

Hello RickMsBi,

Thanks for the update.

Did you take any specific steps to resolve the issue? If yes, please let us know what steps you have taken to resolve the issue so that it will be helpful to other community members having the same issue with the temp DB.

1 answer

RickMsBi 86 Reputation points

2023-08-23T07:31:54.97+00:00

Hi @BhargavaGunnam-MSFT ,

In the end we used the kill command in classical fashion to kill all user processes, then waited the night, next morning it was ok. But it feels weird to have to use the command on the tempdb as the query time limit is 30 minutes and we don't expect this behaviour from a cloud environment.

Regards,

Rick
Please sign in to rate this answer.

1 person found this answer helpful.
BhargavaGunnam-MSFT 27,081 Reputation points Microsoft Employee

2023-08-23T16:08:26.8066667+00:00

Hi @RickMsBi ,

Thank you for the details, and I apologize for the inconvenience you experienced. I suggest logging feedback item in the Synapse Ideas forum for this issue with tempDB

This will help the PG team investigate this issue and improve the service.

https://feedback.azure.com/d365community/forum/9b9ba8e4-0825-ec11-b6e6-000d3a4f07b8
Sign in to comment

Share via

Serverless SQL is not clearing the tempdb after querys now it's full and we cannot use the database anymore (bug?)

1 answer