Databases with MAXSIZE Set

Every DBA needs to appropriately manage database growth.  No DBA wants an application to fail because the database is out of space.  A database can run out of space a number of different ways.  One of those ways is to set the MAXSIZE of a database.  When the database grows to the MAXSIZE the database will not grow anymore and if someone tries to add more rows they will get an error saying the database is full.

There may be legitimate reasons to set the MAXSIZE of a database.  But most of the time you will want your databases to always autogrow when they run out of space. If you are new to your SQL Server environment, or inherited a new database server, you should consider reviewing the databases that have their MAXSIZE limit set.  This way you can review the appropriateness of setting the MAXSIZE for any databases you identify. 

Here is some code that will find all database files that have their max_size set:

-- Database files that have maximum size limit set
SELECT db_name(database_id) DatabaseName, 
       type_desc, 
          name, 
          physical_name, 
          size,
          max_size
FROM sys.master_files 
WHERE 
-- File will grow until the disk is full
max_size <> -1 
-- Log file not set to 2TB
and (type_desc = 'LOG' and max_size <> 268435456);

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles