I want to create a database with Size limitation. I am using sql server 2008r2 edition. Maximum size should be only 20GB. That means it should allow only 20GB space for particular database. How can I do this?

  • Welcome to the site. What is the MS SQL Server version(x,y,z) & Edition(x,y,z). – Md Haidar Ali Khan Oct 16 '19 at 6:58
  • Just as a note, consider what will happen if you reach that space restriction. The application(s) than need more space will fail in those operations. Is that acceptable? Not for us to guess, but for you to ponder. :-) – Tibor Karaszi Oct 16 '19 at 7:33
  • @@Tibor, I just need to create it for two users. They will run sample queries on it. – Jack Oct 16 '19 at 11:03
  • @Md Haidar, it is Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) – Jack Oct 16 '19 at 11:21
  • @Jack, if you are OK with things for the app/users will fail when the database is full, then just follow Yannick's suggestion. Just specify the desired max size for the database file and you're set. – Tibor Karaszi Oct 16 '19 at 14:59

You can set Maxsize on both the data and the log files of your database. If the Max size of your database is 20GB you have to make sure the combined max size of both is 20 GB.

Remember to allocate enough room for the log file, so you don't run into issues early on.

You can do this by going into SSMS, right click your database, properties and go to Files. If you want to script it, it can also be done. Take a look at the BOL to find the syntax : ALTER DATABASE FILE

Perhaps this is also useful for you: Transaction Log Administration Best Practices

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.