40544.The database 'tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

Question

How does tempdb get full in an Azure environment? How can you view tempdb to see the quota size and contents?

Answer 1

Hello,

I found the following about that error:

“Error returned
40544 : The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.  Incident ID: <ID>. Code: <code>.

Limit
150 GB (or less for DBs with smaller quotas)

Type of requests throttled
Inserts, Updates …

Physical Database Space

Current mechanism
When total database size on a machine exceeds 90% of total space available on machine, all databases become read-only. Load balancer ensures the situation is resolved by balancing databases across machines.”
Source: http://social.technet.microsoft.com/wiki/contents/articles/1541.windows-azure-sql-database-connection-management.aspx

To prevent this to happen, please read the following article:

https://blogs.msdn.microsoft.com/psssql/2011/06/15/dipping-my-toes-into-sql-azure-part-2-protection-mechanisms/

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

The information above seems to be for V11 of the service and don't apply to V12 anymore.

TempDB can be filled up with temp tables, TVPs, variables, sorting operations that cannot be done in memory etc. All these are scoped to an open connection and get cleaned up when the connection closes.

Jan

Answer 3

Hello,

We have v12 version of SQL Azure databases but we also got the same error.

"The database "TempDB" reached its size quota. Partition or delete...."

This is a production system and we have no clue how to address this situation. Unable to find any relevant KBs which tells us how to increase the quota for TEMPDB or how to avoid such a situation.

Any pointer will be appreciated.

Regards

Soumik

Answer 4

After a lot of investigation we found that it was not related to our application software at all but was being caused by the Microsoft software that monitors performance of the database and advises on indexes etc. We also found that this same software was causing periodic high usage spikes resulting in poor performance of our application software. The Microsoft software was keeping long running open connections into TempDB so that storage/usage was not being recovered. We switched off the performance monitoring and all the issues stopped.

Answer 5

How did you stop performance monitoring?

Answer 6

How did you stop performance monitoring?

Answer 7

How do you do this?

Answer 8

Hi Paul,

Do you maybe remember what is the name of the software that caused issues?

Thanks,
Veljko

Answer 9

I will clarify a few things on this thread.

SQL Azure (in current v12 architecture and hardware) runs on local SSDs.  This means that you get great IOPS and latency but it is possible to build an application that can consume all of that space.  So, if you create an infinitely large temporary table, you can get this error because you can run out of disk space.

Since Microsoft manages SQL Azure on behalf of customers, some operations (like restarting the server) are managed by us instead of customers.  Since tempdb is re-created on server startup, it is possible to have some objects that are created by users that can "accumulate" in tempdb while the server is up.  If the application code leaks in-scoped objects in tempdb (for example: user tables, not #tables), then these could remain in tempdb while the server is up and take up space.  In the limit, this could cause some applications to run out of space.  If a customer hits this condition and they don't understand why, you can contact Microsoft support to help get the situation resolved.  (Microsoft could initiate a restart of the server/failover to a new replica which would have side effect of re-creating tempdb).  You could also look at the system metadata tables to look for any tempdb tables that have been leaked and delete them yourselves.

The SQL team _does_ have a monitoring system that we use to run the service.  We manage the space used on each node by the system, and in general it is small enough that a customer would never notice this (we budget some space for the system and this is in that space).  So, absent any bug in that code, customers should never notice the monitoring logic we use to help keep these databases running for them.  So, I will suggest that this is unlikely to be an issue for the service about which customers would ever need to consider.

I hope that explains a bit better what is happening in the service and how customers can handle any erroneous issues if something similar happens to them.

Conor Cunningham

Architect, SQL Engine