-1

I have a server running SQL server 2016, and I need to create around 6000 different databases. I've created about 2000 so far, but the server will start throwing errors, all relating to the server being out of memory, such as:

Msg 701, Level 17, State 130, Line 8 There is insufficient system memory in resource pool 'internal' to run this query.

The code is written in c# using the Microsoft.SqlServer.Management.Smo library. In short, it's using SQLRestore to restore a backup of a "master" database to a new database with a different name.

After restarting the SQL Server service, I'm able to create 20 to 30 databases before encountering the same error.

How do I avoid this issue?

Some info about the server:

  • Windows Server 2012 R2
  • SQL Server 2016 13.0.4001.0 (x64)
  • 24 GB total ram, 16 GB allocated to SQL server
  • This is a test server, the production server has considerably more RAM. Each database is used to hold a different customer's data. This isn't a production server; it's a testing environment that we're using before going to production.
Improve this question
3
  • 3
    Can you post the script you're using to create the databases? My guess (and this is just a guess) is that we've got something other than a create-database script in there, like we're joining to a list of existing databases, perhaps.
    – Brent Ozar
    Sep 11 '18 at 22:21
  • I shudder to think of the administration overhead required to support 6000+ separate database for 6000+ separate customers. How will you upgrade them all when new tables/columns are needed? Much easier to do multi-tenant in one database with a "customer_id" column in all fundamental tables containing customer information. IMHO.
    – Colin 't Hart
    Sep 12 '18 at 10:06
  • 2
    You may want to take a look at this answer whcih talks about the cons of having thousands of databases per instance. In short, try very hard not to do that. If you're insistent on doing that, you need your test box to match production exactly, i.e. you need the same RAM on test as in production, and it needs to be way way more than 16 GB. My freaking laptop has 32 GB.
    – Hannah Vernon ♦
    Sep 12 '18 at 14:39
2

You are correct: having thousands of databases on your hardware won't work.

I understand that you're doing a proof-of-concept, but think about it differently.

If you're testing application logic or database maintenance, you don't need 6,000 databases on that server in order to do that level of testing. You should be able to get by with, say, 100.

If you're testing performance, you'll need to test on the kind of hardware that you'd be using in production. Even without end user queries, your simple regular background jobs of things like backups, stats updates, and checkdb will lead to dramatic performance issues.

If you're testing high availability with database mirroring or Always On Availability Groups, be aware that each additional database consumes resources even when it's not doing anything.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.