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.