Setting maximum database file sizes in SQL Server is something you’d ideally not have to set, but it’s a good last resort option available if you need it.

The reason I say this is because, you never want a production database to stop updating/inserting data due to a size cap on the database. However, if something is causing unpredictable disk space issues, you should really try stop it from happening again at source… Sometimes that’s not so easy though.

For example, if random user queries are consuming massive amounts of TempDB space and throwing disk space monitoring alerts, and let’s say, you’re actively killing their queries to avoid the disk becoming full. That SQL Server instance could have many other live databases, and we could also say there’s more than 10 users running ad hoc queries throughout the day.

Those users causing the unexpected will need to understand why this is happening before you can sleep easy. How long will it take to get everyone up to speed? We’ll need to show them what happens when they try throw a 100GB table into a temporary table, or how to measure the size of table they’re working with!

If they’re remote users, things could be all the more difficult to manage. What information will you try send them within that “Sorry I killed your SPID” email?

There’s many variables, so it’s definitely one of those “it depends” scenarios in the land of SQL Server. My point here is, you might have a legit reason to set a cap on a database, but it should be more considered a short-term measure in most cases.

Below, is a simple guide on how to set a size cap on a database in SQL Server.

Setting Max Database Size

Right click database & select Properties.

Click into the Files tab on the left-hand menu and click the button as shown below.

Don’t go setting TempDB files to 100MB like I’m doing above. This is test stuff.

Alternatively, we can use the ALTER DATABASE statement.

As you can see above, you can enter these values in GB/MB/KB.