Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted June 4, 2018

Databases with MAXSIZE Set

By Greg Larsen

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, 
FROM sys.master_files 
-- 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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM