-2

When trying to create 100 SQL databases from 100 sessions at about the same time with the following statement:

Format(L"CREATE DATABASE %s ", name)

Some of the session failed to create database with this error:

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Any idea what might be the cause?

MOMING
  • 1
  • 2
  • Rather strange thing to do from *different sessions* at the same time, but at a guess you may be having deadlocks against `master` database, what are the "related errors", are they in the log? – Charlieface Apr 11 '21 at 11:16
  • I'm also interested in the "related errors", but there is nothing in the ERRORLOG, possibly because the database was not created. I only got this information from the exception caught when trying to run this statement with OLEDB Execute(...). – MOMING Apr 11 '21 at 11:44
  • Are you using C# with SqlClient? Then what messages do you get if you try this https://stackoverflow.com/a/48520271/14868997 – Charlieface Apr 11 '21 at 11:55

2 Answers2

6

To answer the title. From Maximum capacity specifications for SQL Server:

SQL Server Database Engine object Maximum sizes/numbers SQL Server (64-bit)
Databases per instance of SQL Server 32,767

As for the error, without having the full code you are running, and the names of the databases/files you are trying to create, this is impossible for us to know. Perhaps you are trying to create some databases with a long name and thus the file name isn't valid. Perhaps there isn't enough space on host (though I would expect a different error then). Perhaps the name isn't a valid name; there are characters that aren't allowed in filenames for example.

The real question (in my opinion) is why do you need 100 databases to be created in one go? Seems like you need to rethink your design.

Thom A
  • 88,727
  • 11
  • 45
  • 75
0

By design when we create a database we need exclusive lock on model. When SQL Server creates a new database, "The SQL Server uses a copy of the model database to initialize the database and its metadata". Also, users could create, modify, and drop objects in the Model database. As such, it is important to exclusively lock the model database to prevent copying the data in change from the Model database. Otherwise, there is no guarantee that the content copied from the Model database are consistent and valid. [1]: https://learn.microsoft.com/en-us/archive/blogs/poojakamath/could-not-obtain-exclusive-lock-on-database-model-retry-the-operation-later-create-database-failed

MOMING
  • 1
  • 2