Getting error creating database in SQL Elastic Pool, but can do it through the portal

Hi @Joe DeNicola,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

As I understand, the issue is related to the max size constraints when trying to create the database directly in the Elastic Pool.

However, you can achieve your goal by using a two-step process with T-SQL:

1.Create the database copy outside the pool:

CREATE DATABASE DBCopy AS COPY OF DBProd;

2.Move the database into the pool:

ALTER DATABASE DBCopy MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL (name = MyPool));

This approach should work without running into the MAXSIZE issue. By first creating the copy outside the pool and then moving it into the pool.

For more information, please refer the documents:

https://zcusa.951200.xyz/en-us/azure/azure-sql/database/elastic-pool-manage?view=azuresql

https://zcusa.951200.xyz/en-us/sql/t-sql/statements/create-database-transact-sql?view=azuresqldb-current&preserve-view=true&tabs=sqlpool#copy-a-database

I hope this information helps. Please do let us know if you have any further queries.

Thanks, I will try this.

This was working when I had the Elastic Pool set to use vCores, with vCores = 4, but these databases are lightly used and we do not need 4 vCores. But with 2 vCores, it said the pool did not have enough storage.

This problem started when I changed it to use DTUs.

One problem with the method you suggest is that there does not seem to be a way to know when the COPY is complete. The database is visible almost immediately, but there are no tables in it until all of the data is copied. The only ways I can think of is to (1) wait an amount of time before moving it to the pool and hope that the copy is complete, or (2) poll the database until a table exists, or (3) try to do the move anyway and see if it still completes.

Any other way to know when the COPY has completed?

Hi @Joe DeNicola,

Thanks for getting back to me.

Any other way to know when the COPY has completed?

To know when the copy process is complete, you can use the following methods:

You can query the sys.databases and sys.dm_database_copies view to check the status of the database copy operation.

SELECT 
    name, 
    state_desc 
FROM 
    sys.databases 
WHERE 
    name = 'DBCopy';

SELECT 
    database_id, 
    start_date, 
    modify_date, 
    percent_complete 
FROM 
    sys.dm_database_copies 
WHERE 
    database_id = DB_ID('DBCopy');

This will give you the status and percentage completion of the copy operation.

For more information, please refer the documents:

https://zcusa.951200.xyz/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver16

https://zcusa.951200.xyz/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-copies-azure-sql-database?view=azuresqldb-current

https://www.sqlshack.com/four-different-methods-to-copy-your-azure-sql-database/

I hope this information helps. Please do let us know if you have any further queries.

OK, I will look into that.

I also found that when it does the copy, it is creating the copy with the same service tier as the original database (in my case, it is 10 vCores). So when I try to use the ALTER DATABASE statement to move it into the Elastic Pool, it fails because of MAXSIZE of the copy of the database.

Hi @Joe DeNicola,

Thanks for the update.

Before moving the database into the elastic pool, you need to adjust its service tier and size to fit within the pool’s limits. This can be done using the ALTER DATABASE statement.

ALTER DATABASE DBCopy MODIFY (SERVICE_OBJECTIVE = 'S3', MAXSIZE = 300 GB);

Adjust the SERVICE_OBJECTIVE and MAXSIZE parameters according to the limits of your elastic pool. Finally, move the database into the elastic pool.

ALTER DATABASE DBCopy MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL (name = MyPool));

I hope this information helps. Please do let us know if you have any further queries.