'SQL Azure tempdb size quota on index create

I need to create an index on a 800 million row heap table with no partitioning

CREATE NONCLUSTERED INDEX [IX] ON [dbo].[table]
(
    columns....
)
INCLUDE(columns) WITH (
STATISTICS_NORECOMPUTE = OFF
, DROP_EXISTING = OFF
, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
, SORT_IN_TEMPDB =OFF
, ONLINE = OFF) ON [PRIMARY]
GO

This command results in:

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

Before I start trying batch loading into a new table, cutoff indexing, and partitioning, is there any way to force SQL Azure to create an index?

azure-sql-databasedatabase-administration


Solution 1:[1]

CREATE INDEX require additional temporary disk space to use during the operation, and permanent disk space to store the new index structure or structures.

Detect and Prevent unexpected growth of Tempdb database help to keep the TempDB database in a healthy state and prevent any unexpected growth of the database, in addition to the procedure that can be followed to detect this unexpected growth once occurred.

configure the index create memory server configuration option : The index create memory option controls the maximum amount of memory initially allocated for sort operations when creating indexes

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 PratikLad-MT

Related Questions