Czy ta strona była przydatna?
Twoja opinia na temat zawartości jest dla nas bardzo ważna. Powiedz nam co myślisz.
Dodatkowe opinie?
Pozostało znaków: 1500
Eksportuj (0) Drukuj
Rozwiń wszystko
EN
Ta zawartość nie jest dostępna w wymaganym języku. Wersja w języku angielskim znajduje się tutaj.

Azure SQL Database Resource Limits

Updated: March 12, 2015

Azure SQL Database monitors the usage of the shared resources (transaction log, I/O, and so on) to keep databases within set resource boundaries. This resource boundary or threshold is called the resource limit, and when the resource usage by the clients exceeds these limits, either at a tenant or physical node level, Azure SQL Database responds by managing the resource usage, which results in connection losses or request denials.

The following table provides a summary of the limits for each resource beyond which Azure SQL Database denies request or terminates connections to the affected resource, and an error code is returned.

ImportantImportant
Sometimes the same error code is returned for multiple limitation conditions for a resource. These conditions are identified as states in the error message. For example, the following error messages are displayed for the Transaction Log Length resource, each one having the same error code/message but different State values based on different limitation conditions:

Msg 40552, Level 17, State 1, Line 1
The session has been terminated because of excessive transaction log space usage.
Try modifying fewer rows in a single transaction.

-----------------------------------------------------------

Msg 40552, Level 17, State 2, Line 1
The session has been terminated because of excessive transaction log space usage.
Try modifying fewer rows in a single transaction.

The table below and the description thereafter contain the limits and description for each state in such error codes. Click on the resource name in the table to jump to the respective description later in this topic.

 

Resource Limit Error code returned

Database Size

Depends on the database quota (MAXSIZE)

40544

Logins

Login limits depend on the performance level of the database. For details, see Azure SQL Database Service Tiers and Performance Levels.

10928

Memory Usage

16 MB memory grant for more than 20 seconds

40553

Sessions

Session limits depend on the performance level of the database. For details, see Azure SQL Database Service Tiers and Performance Levels.

10928

Tempdb

State 1: 5 GB of tempdb space

State 2: 2 GB per transaction in tempdb

State 3: 20% of total log space in tempdb

40551

Transaction Duration

State 1: 24 hours

State 2: 20 seconds if a transaction locks a resource required by an underlying system task

40549

Transaction Lock Count

1 million locks per transaction

40550

Transaction Log Length

State 1: 2 GB per transaction

State 2: 20% of total log space

40552

Worker Threads (max concurrent requests)

The maximum number of concurrent requests is dependent on the performance level of the database. For details, see Azure SQL Database Service Tiers and Performance Levels.

  • 10928

  • 10929

TipTip
When resource limits prevent queries to analyze database performance problems, you may need to use the dedicated administrator connection (DAC) which is available beginning with Azure SQL Database V12. For more information about using the DAC, see Diagnostic Connection for Database Administrators.

Return to Top

 

Request Denial/Connection Loss Mechanism Error code returned Limit Type of requests denied Recommendation

When the database space allotted to a user database is full, the user gets a database full error.

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

Depends on the database quota (MAXSIZE).

Non-Select DML (Insert, Update, Merge that inserts or updates).

Use DELETE/DROP statements to remove data from the database until the size of the database is under the limit.

Return to Top

 

Request Denial/Connection Loss Mechanism Error code returned Recommendation

SQL Database governs the limit on the number of concurrent logins that can be established to a database. When the concurrent login limit for a database is reached, new login requests to the database are denied and error code 10928 is returned.

10928: Resource ID: 3. The %s limit for the database is %d and has been reached. See http://go.microsoft.com/fwlink/?LinkId=267637 for assistance.

Note: The Resource ID value in the error message indicates the resource for which limit has been reached. For logins, Resource ID = 3.

10928: Check dm_exec_connections to view which user connections are currently active.

Back-off and retry login after 10 seconds.

Return to Top

 

Request Denial/Connection Loss Mechanism Error code returned Limit Type of requests denied Recommendation

When there are sessions waiting on memory grants for 20 seconds or more, sessions consuming greater than 16 MB of memory grant for more than 20 seconds are terminated in the descending order of time the resource has been held, so that the oldest session is terminated first. Termination of sessions stops as soon as the required memory becomes available.

40553: The session has been terminated because of excessive memory usage. Try modifying your query to process fewer rows.

More than 16 MB of memory grant for more than 20 seconds.

Queries that consume memory grants, which include queries that use sorts and hash joins.

Perform query tuning on queries that require sorts and/or hash joins.

Return to Top

 

Request Denial/Connection Loss Mechanism Error code returned Recommendation

SQL Database governs the limit on the number of concurrent sessions that can be established to a database. When concurrent session limit for a database is reached, new connections to the database are denied and user will receive error code 10928. However, the existing sessions to the database are not terminated.

  • For Web/Business edition database, the concurrent session limit is internal.

  • For Basic, Standard, and Premium databases, the concurrent sessions limit varies depending on the performance level of the database. For details, see Azure SQL Database Service Tiers and Performance Levels.

10928: Resource ID: 2. The %s limit for the database is %d and has been reached. See http://go.microsoft.com/fwlink/?LinkId=267637 for assistance.

noteNote
The Resource ID value in the error message indicates the resource for which limit has been reached. For sessions, Resource ID = 2.

10928: Check dm_exec_requests to view which user requests are currently executing.

Return to Top

 

Request Denial/Connection Loss Mechanism Error code returned Limit Type of requests denied Recommendation

Your requests on tempdb can be denied due to any of the following three conditions:

  • State 1: When a session uses more than 5 GB of tempdb space, the session is terminated.

  • State 2: Transactions in tempdb with logs beyond 2 GB size are truncated. Example operations that can consume log space in tempdb: insert, update, delete, merge, create index.

  • State 3: The uncommitted transactions in tempdb can block the truncation of log files. To prevent this, the distance from the oldest active transaction log sequence number (LSN) to the tail of the log (current LSN) in tempdb cannot exceed 20% of the size of the log file. When violated, the offending transaction in tempdb is terminated and rolled back so that the log can be truncated.

40551: The session has been terminated because of excessive tempdb usage. Try modifying your query to reduce the temporary table space usage.

State 1: 5 GB of tempdb space

State 2: 2 GB per transaction in tempdb

State 3: 20% of total log space in tempdb

Any DDL or DML statements on tempdb.

Modify queries to reduce the temporary table space usage, drop temporary objects after they are no longer needed, truncate tables or remove unused tables.

Reduce the size of data in your transaction in tempdb by reducing the number of rows or splitting the operation into multiple transactions.

Return to Top

 

Request Denial/Connection Loss Mechanism Error code returned Limit Type of requests denied Recommendation

Transactions request locks on resources like rows, pages, or tables, on which the transaction is dependent and then free the locks when they no longer have a dependency on the locked resources. Your requests can be denied due to any of the following two conditions:

  • State 1: If a transaction has been running for more than 24 hours, it is terminated.

  • State 2: If a transaction locks a resource required by an underlying system task for more than 20 seconds, it is terminated.

40549: Session is terminated because you have a long-running transaction. Try shortening your transaction.

State 1: 24 hours

State 2: 20 seconds if a transaction locks a resource required by an underlying system task

Any transaction that has been running for more than 24 hours or any DDL or DML statements that takes a lock, which results in blocking a system task.

Operations against SQL Database should not block on user input or have other dependencies that result in long-running transactions.

Return to Top

 

Request Denial/Connection Loss Mechanism Error code returned Limit Type of requests denied Recommendation

Sessions consuming greater than one million locks are terminated.

40550: The session has been terminated because it has acquired too many locks. Try reading or modifying fewer rows in a single transaction. 

1 million locks per transaction

Any DDL or DML statements.

Following DMVs can be used to monitor transactions:

  • sys.dm_tran_active_transactions

  • sys.dm_tran_database_transactions

  • sys.dm_tran_locks

  • sys.dm_tran_session_transactions

Depending on the type of application, it may be possible to use coarser grain lock hints, like PAGLOCK or TABLOCK, to reduce the number of locks taken in a given statement/transaction. Note that this can negatively impact application concurrency.

Return to Top

 

Request Denial/Connection Loss Mechanism Error code returned Limit Type of requests denied Recommendation

Your requests could be denied due to any of the following two conditions:

  • State 1: SQL Database supports transactions generating log of up to 2 GB in size. Transactions with logs beyond this limit are truncated. Example operations that can consume log space in this volume: insert, update, delete, merge, create index.

  • State 2: The uncommitted transactions can block the truncation of log files. To prevent this, the distance from the oldest active transaction log sequence number (LSN) to the tail of the log (current LSN) cannot exceed 20% of the size of the log file. When violated, the offending transaction is terminated and rolled back so that the log can be truncated.

40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

State 1: 2 GB per transaction

State 2: 20% of total log space

Any DDL or DML statements.

For row operations, reduce the size of data in your transaction, for example by reducing the number of rows or splitting the operation into multiple transactions.

For table/index operations that require a single transaction, ensure that the following formula is adhered to: number of rows affected in table * (avg size of field being updated in bytes + 80) < 2 GB(In case of index rebuild, avg size of field being updated should be substituted by avg index size).

Return to Top

 

Request Denial/Connection Loss Mechanism Error code returned Recommendation

SQL Database governs the limit on the number of worker threads (concurrent requests) to a database. Any database with more than the allowed limit of concurrent requests will receive error 10928, and further requests on this database can be denied.

  • For Web/Business edition database, the maximum limit of concurrent requests is 180. Beyond this limit, you will receive error 10928.

    Also, this limit (180) is only a maximum cap, and there is no guarantee that a Web/Business edition database will get requests up to this limit if the system is too busy. In case the system is too busy, it is possible that even fewer worker threads are available for the Web/Business edition database and user will receive error 10929. This is expected to be a rare occurrence.




    For Basic, Standard, and Premium databases, the concurrent request limit varies depending on the performance level of the database. For details, see Azure SQL Database Service Tiers and Performance Levels.

10928: Resource ID: 1. The %s limit for the database is %d and has been reached. See http://go.microsoft.com/fwlink/?LinkId=267637 for assistance.

10929: Resource ID: 1. The %s minimum guarantee is %d, maximum limit is %d and the current usage for the database is %d. However, the server is currently too busy to support requests greater than %d for this database. See http://go.microsoft.com/fwlink/?LinkId=267637 for assistance. Otherwise, please try again later.

noteNote
The Resource ID value in both the error messages indicates the resource for which limit has been reached. For worker threads, Resource ID = 1.

10928: Check dm_exec_requests to view which user requests are currently executing.

10929: Back-off and retry request after 10 seconds.

ImportantImportant
  • Errors due to governance on worker threads (10928/10929) replace the original engine throttling error (40501) for worker threads. Under normal conditions, users should no longer receive engine throttling error for worker threads.

  • In certain scenarios like the usage of federated database feature, it is possible to hit the worker thread cap error (10928) at the time of signing in to a database as this operation would utilize a worker thread underneath Connection.Open call. This may put the application above the worker thread cap threshold. Applications should have built-in logic to handle this error appropriately to handle such cases.

Return to Top

See Also

Pokaż:
© 2015 Microsoft