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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

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, 
       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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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