-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.
Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Justin Rhodes
  • 11
  • 1

1 Answers1

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.

Brent Ozar
  • 43,325
  • 51
  • 233
  • 390