Synapse create lake table from data lake giving error: Failed to execute query. Error: CREATE DATABASE failed. User database limit has been already reached (20).

Question

Synapse create lake table from data lake giving error: Failed to execute query. Error: CREATE DATABASE failed. User database limit has been already reached (20).

Leslie 26

I have an existing folder with delta log and parquet files. This was working up until yesterday. I could via workspace/database gui - add a table via "from data lake" and select the folder for that table and it previously would create schema for table. It is now failing with the above error.

This is to create a table in a lake database.

KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-08-12T22:57:45.947+00:00

Hello @Leslie ,

Thanks for the question and using MS Q&A platform.

As per my understanding, you are receiving the above reported error while creating a Lake database table from Synapse Studio UI option "From Data lake" so to work around you have done it directly in the JSON to create a table. Please correct me if my understanding is wrong.

I agree that the error message is wired which says Database creation failed while you are actually trying to create a table in Lake database.

In order to test if there is a limit on table creation, I tried creating more that 20 tables in my Lake database but wasn't able to reproduce the error message you are seeing. I was able to use the UI from data lake feature to create new tables in Lake database.

Could you please try to close reopen the studio and see if you able to reproduce the issue consistently while trying to create a table? I'm also initiating a private message requesting few additional details. Please respond accordingly so that we can have a deeper look into it.

Thank you
KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-08-30T23:26:58.98+00:00

Hello @Leslie ,

Following up to see if you have a got a chance to file a support ticket for deeper analysis of this issue. In so would you please share the SR# with us for tracking it internally.

Thank you
Leslie 26 Reputation points

2022-08-31T19:29:28.677+00:00

Hi Kranthi - so it appears that the count in the sql database is forcing this error!!! I deleted the _schema ones getting the sql databases under the 20 count. I then tested the creation of the table from data lake in the gui for a lake database table and no error! So yes there is something in the code on lake table create that is checking the count on sql databases. Hopefully you can recreate it that way - so get 20 sql databases in and then go to lake database model designer and try and create the table from data lake. @KranthiPakala-MSFT
Leslie 26 Reputation points

2022-08-31T19:31:45.067+00:00

Hi Kranthi - so it appears that the count in the sql database is forcing this error!!! I deleted the _schema ones getting the sql databases under the 20 count. I then tested the creation of the table from data lake in the gui for a lake database table and no error! So yes there is something in the code on lake table create that is checking the count on sql databases. Hopefully you can recreate it that way - so get 20 sql databases in and then go to lake database model designer and try and create the table from data lake. @KranthiPakala-MSFT
KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-08-31T19:50:26.437+00:00

Hello @Leslie ,

Thanks for sharing your findings. This will be helpful to reproduce the problem and escalate to respective product owners.
Appreciate much for your inputs. Will keep you posted as we make progress on this issue.
KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-09-01T21:56:53.743+00:00

Hello @Leslie ,

I tried creating 20 SQL databases (Serverless which also include temp databases), then tried creating table in Lake Database from UI and it worked without any issues. Am I missing something or is it something happening specific to your environment? Could you please confirm if you are able to reproduce the issue consistently?
KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-09-02T23:45:11.547+00:00

Hello @Leslie ,

Just checking in to see you have got a chance to verify if you are able to reproduce the issue consistently. If so, could you please file a support ticket for deeper investigation and do share the SR# with us here.

Thank you
KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-09-05T23:23:30.187+00:00

Hello @Leslie ,

Following up to check if you any updates on the above comments. Please do share the SR# if you have filed a support ticket for this issue. In case if you need assistance filing a support ticket please let us know here.

Thank you.

KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-08-12T22:57:45.947+00:00

Hello @Leslie ,

Thanks for the question and using MS Q&A platform.

As per my understanding, you are receiving the above reported error while creating a Lake database table from Synapse Studio UI option "From Data lake" so to work around you have done it directly in the JSON to create a table. Please correct me if my understanding is wrong.

I agree that the error message is wired which says Database creation failed while you are actually trying to create a table in Lake database.

In order to test if there is a limit on table creation, I tried creating more that 20 tables in my Lake database but wasn't able to reproduce the error message you are seeing. I was able to use the UI from data lake feature to create new tables in Lake database.

Could you please try to close reopen the studio and see if you able to reproduce the issue consistently while trying to create a table? I'm also initiating a private message requesting few additional details. Please respond accordingly so that we can have a deeper look into it.

Thank you
KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-08-30T23:26:58.98+00:00

Hello @Leslie ,

Following up to see if you have a got a chance to file a support ticket for deeper analysis of this issue. In so would you please share the SR# with us for tracking it internally.

Thank you
Leslie 26 Reputation points

2022-08-31T19:29:28.677+00:00

Hi Kranthi - so it appears that the count in the sql database is forcing this error!!! I deleted the _schema ones getting the sql databases under the 20 count. I then tested the creation of the table from data lake in the gui for a lake database table and no error! So yes there is something in the code on lake table create that is checking the count on sql databases. Hopefully you can recreate it that way - so get 20 sql databases in and then go to lake database model designer and try and create the table from data lake. @KranthiPakala-MSFT
Leslie 26 Reputation points

2022-08-31T19:31:45.067+00:00

Hi Kranthi - so it appears that the count in the sql database is forcing this error!!! I deleted the _schema ones getting the sql databases under the 20 count. I then tested the creation of the table from data lake in the gui for a lake database table and no error! So yes there is something in the code on lake table create that is checking the count on sql databases. Hopefully you can recreate it that way - so get 20 sql databases in and then go to lake database model designer and try and create the table from data lake. @KranthiPakala-MSFT
KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-08-31T19:50:26.437+00:00

Hello @Leslie ,

Thanks for sharing your findings. This will be helpful to reproduce the problem and escalate to respective product owners.
Appreciate much for your inputs. Will keep you posted as we make progress on this issue.
KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-09-01T21:56:53.743+00:00

Hello @Leslie ,

I tried creating 20 SQL databases (Serverless which also include temp databases), then tried creating table in Lake Database from UI and it worked without any issues. Am I missing something or is it something happening specific to your environment? Could you please confirm if you are able to reproduce the issue consistently?
KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-09-02T23:45:11.547+00:00

Hello @Leslie ,

Just checking in to see you have got a chance to verify if you are able to reproduce the issue consistently. If so, could you please file a support ticket for deeper investigation and do share the SR# with us here.

Thank you
KranthiPakala-MSFT 35,597 Reputation points Microsoft Employee

2022-09-05T23:23:30.187+00:00

Hello @Leslie ,

Following up to check if you any updates on the above comments. Please do share the SR# if you have filed a support ticket for this issue. In case if you need assistance filing a support ticket please let us know here.

Thank you.

Answer 1

Vinodh247 2,191

Hi Leslie,

Thanks for reaching out to Microsoft Q&A.

You are facing issue in creating table but the error states that 'Create database failed'. This happens when you have reached your database creation limits, see the below link from microsoft related to this.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand?tabs=x80070002#cant-create-a-database

Please Upvote and Accept as answer if the reply was helpful.

Leslie 26 Reputation points

2022-08-12T12:55:13.747+00:00

I'm well under the count of databases and I am creating a table. So this 1) a false message 2) it is preventing me from using the gui modeling feature from adding a table this way. If it is a bug it is a bug - I believe it is. At this point I'm having to do it directly in the JSON.

Synapse create lake table from data lake giving error: Failed to execute query. Error: CREATE DATABASE failed. User database limit has been already reached (20).

1 answer

Activity