SQLDW: Could not allocate a new page for database

Issue:

Recently I faced an issue where Stored Procedure running on SQLDW failing with error as follow

An error occurred in Stored Procedure Activity execution. Diagnostic details: Database operation failed on server ‘xxxyyyy123.centralus1-a.worker.database.windows.net,11087' with SQL Error Number '1101'. Error message from database execution : Could not allocate a new page for database 'Distribution_11' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

 

Resolution:

After working with SQLDW Support team found that issue can occur when you hit max size of the database which is by default 10TB(if not stated while creating a database) and it can be resolved by increasing the size of database using following command:-

ALTER DATABASE [SQLDWDB] MODIFY ( MAXSIZE=245760 GB );

In this case I set it to 240 TB but you can size it based on your requirement.

Note:

Before you run these examples, make sure the database you are altering is not the current database. The current database must be a different database than the one you are altering, therefore ALTER must be run while connected to the master database.

 

Credits:

P V Suhas from SQLDW Support Team