SALES: 1-800-867-1380

Azure SQL Database Resource Limits

Updated: June 26, 2014

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.

In This Topic

Resource Limits Summary Table

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

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

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 Log Length

State 1: 2 GB per transaction

State 2: 20% of total log space

40552

Memory Usage

16 MB memory grant for more than 20 seconds

40553

Database Size

 

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

When the database space allotted to 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

Transaction Duration

 

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

Transaction Lock Count

 

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

Tempdb

 

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

Transaction Log Length

 

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

Memory Usage

 

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

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft