10 GByte size limit MS SQL server

Question

We have a measurement software, which stores values in a MS SQL database with 10 GByte size limit. When the 10 GByte limit is reached, the program automatically backups the DB and creates a new one.

Now we want to reduce the size of the DB for easier handling. According to the software company, there is no way to configure the software to automatically backup the DB at sizes below 10 GByte. However, it may be possible to limit the size of the database by configuring the SQL server. Is there a way to globally set the max DB size in SQL server to a value below 10 GByte?

Thanks and best regards,
anthrazius

Answer 1

I assume that the 10 GB limit in your case is due to that you are running SQL Server Express, which caps the database size of license reasons. (It's after all free and still licensed for production usage.)

When you create a database, you can specify a max size for the data file:

CREATE DATABASE MySmallDatabase ON 
    (NAME = 'MySmallDatabase', 
     FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA\MySmallDatabase.mdf', 
     MAXSIZE = 2 GB)

You could also have a DDL trigger that fires on ALTER DATABASE to change the max size.

But I am not sure that this is a good idea. I suspect that will happen is that when you reach the limit, you will get a "file full" error, and your application may not react, because it's only looking for that 10 GB limit.

Answer 2

Hi @anthrazius,

• To set the file size in SQL Server Management Studio:
  • Right-click the database that you would like to limit the size and get properties
    • Click the Files link in the menu on the left
      • Click the … button under Autogrowth / maxsize and set the size accordingly
• To set the file size in T-SQL:

ALTER DATABASE [MyDataBase] MODIFY FILE ( NAME = N’MyDataFile’, MAXSIZE = 50000 MB );
GO

Note: [MyDataFile] is the logical name of your data file, not the physical file name on the filesystem.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Now we want to reduce the size of the DB for easier handling.

Nowadays 10 GB is very small and easy to handle.

If a new database gets create, SQL Server will copy the system database "model" as template, so you could modify "model" database setting to limit the size for new databases.