Temporary Tables on SQL Azure & Error 40544

On this article we are showing some differences in behavior on how temporary tables are handled and supported on SQL Azure Databases vs SQL Server.

First, global temporary tables are not supported on SQL Azure databases. For example a statement like this:

CREATE TABLE ##TempAzureTable(Attribute1 int)
GO

Will fail to execute showing the following error:


Msg 40516, Level 15, State 1, Line 1
Global temp objects are not supported in this version of SQL Server.


SQL Azure Database supports creating temporary tables same as in SQL Server.

CREATE TABLE #TempAzureTable(Attribute1 int)
GO

You can verify the existence of this temporary table using below query.

SELECT *
FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..#TempAzureTable')
AND xtype = N'U'

You can also explore the existence and size of other temporary objects on TempDB by using the following query:


SELECT t.name AS ObjectName,
ps.row_count AS NumberOfRows,
ps.used_page_count * 8 AS UsedSizeKB,
ps.reserved_page_count * 8 AS RevervedSizeKB
FROM tempdb.sys.partitions AS p
INNER JOIN tempdb.sys.dm_db_partition_stats AS ps
ON p.partition_id = ps.partition_id
AND p.partition_number = ps.partition_number
INNER JOIN tempdb.sys.tables AS t
ON ps.object_id = t.object_id
ORDER BY t.name;


The above query returned just the temporary table created during this article when executed on a SQL Server on premise instance but returned 118 temporary objects that already existed on TempDB when was executed from a SQL Azure database, some of those temporary objects are related to extended events and monitoring jobs created by Microsoft monitoring system.

Additionally, the above query cannot be executed from the master database of a SQL Azure Database server, but from a user database. When executed from master database the following error may appear:

Msg 262, Level 14, State 1, Line 1
VIEW DATABASE STATE permission denied in database 'tempdb'.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.


When temporary tables are created inside a stored procedure the temporary table exist only during the stored procedure execution even when is not explicitly dropped inside the stored procedure. This can be demonstrated using below script.

ALTER PROCEDURE CreateTempAzureTable AS

CREATE TABLE #TempAzureTable(Attribute1 int)
INSERT INTO #TempAzureTable(Attribute1) values (1)
INSERT INTO #TempAzureTable(Attribute1) values (2)

SELECT *
FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..#TempAzureTable')
AND xtype = N'U'
GO
EXEC CreateTempAzureTable

SELECT *
FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..#TempAzureTable')
AND xtype = N'U'

The TempDB on a SQL Azure Database server can be filled up with temporary objects (including tables and variables) and sort operations created by user programs and processes. These objects are removed automatically when the connection that created them is closed.

In case of the appearance of the error 40544 (Error returned. 40544 : The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions) we can use below query to identify temporary tables and then proceed to delete (drop) them from TempDB.

SELECT *
FROM tempdb..sysobjects
WHERE xtype = N'U'


Conor Cunningham (SQL Server Engine Architect - Microsoft) explains more in details the circumstances under this error appears and how to deal with it.

“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.”
Source: here.
 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.