none
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?
    Wednesday, March 16, 2016 1:51 PM

Answers

All replies

  • 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


    Wednesday, March 16, 2016 5:34 PM
  • 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

    Monday, April 04, 2016 4:10 PM
  • 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

    Tuesday, August 23, 2016 8:52 AM
  • 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.

    Tuesday, August 30, 2016 8:48 AM
  • How did you stop performance monitoring?
    Wednesday, September 07, 2016 9:02 AM
  • How did you stop performance monitoring?
    Wednesday, September 07, 2016 9:31 AM
  • How do you do this?
    Tuesday, September 27, 2016 5:05 AM
  • Hi Paul,

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

    Thanks,
    Veljko

    Thursday, September 29, 2016 12:01 PM
  • 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

    Friday, February 03, 2017 6:37 PM