How to limit a database size on SQL Server

In an ideal world, we would not need to focus on the size of a database, but in most cases, we need to consider limiting our database size’s. If such considerations are not made, databases can grow faster than we expect and cause a whole slew of problems—architecture, availability, maintenance, performance tuning, just to name a few of them.

In general, large databases usually require powerful hardware to run—or at the very least, they require a lot of storage.  For this reason, it’s common to find projects where you will need to limit the size of the databases.  Test/development databases are a good example of this.  If you fail to watch these closely, they can cause major problems due to the amount of data accumulated. 

Here’s an example that uses a sandbox database that I created for developers to use for testing.  I needed to limit the database size on this database so that it did not exceed 50gb.   Since the database had already been created, I needed to alter the database and set the max size.  

There are two ways you can do this.  You can use SSMS or with a TSQL statement. 

Below, we’ll go over both of these methods.

  • 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

limit the database size

    • Click the Files link in the menu on the left

limit the database size

    • Click the … button under Autogrowth / maxsize and set the size accordingly

limit the database size

limit the database size

  • 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. You can find the logical file names of your database files with the sys.database_files system view:

USE MyDataBase
SELECT name
    FROM sys.database_files
WHERE type_desc = ‘ROWS’;
GO

The value in the name column is the logical name of the database file.

Using this method, I was able to reduce the size of my database to the desired parameters.  

For more SQL tips, be sure to check out our other blogs.   If you’re interested in my provided SQL services, check out Anyon Consulting LLC

 

Scroll to top