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