Get DB Maximum Capacity Query?

Posted on 2010-11-17
Last Modified: 2012-05-10
I would like to run a query to determine the maximum DB size. This is for a Windows app that will be installed on various servers.
Question by:pointeman
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 34160655
DO you mean the maximum capacity for SQL server databases??
anyway Max size is "huge" far larger than anything you'll encounter (heck, just
buying the disks to reach the max size will break most budgets).

check this

or you can use this command to get the current DB size, but there's no such thing as maximim size, as databases are has auto growth, unless you turned it off !
EXEC sp_spaceused

Open in new window

hope that helps
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 83 total points
ID: 34160835
And it should be added that the MSDE/Express Editions have limitations of 2GB, 4GB and 10GB, depending on the version.

Assisted Solution

subhashpunia earned 83 total points
ID: 34163092
For SQL Server 2008 the max size limit of database is 524,272 terabytes (TB).
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

LVL 69

Expert Comment

by:Scott Pletcher
ID: 34164634
I would think he means the largest existing db on the instance.
LVL 21

Accepted Solution

Jason Yousef, MS earned 84 total points
ID: 34165554
OK, then run that, to get list of DBs along with Size, in DESC order
use master
        DATABASE_NAME   = db_name(s_mf.database_id),
        DATABASE_SIZE   = convert(int,
                                    case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
                                    when convert(bigint, sum(s_mf.size)) >= 268435456
                                    then null
                                    else sum(s_mf.size)*8 -- Convert from 8192 byte pages to Kb
        sys.master_files s_mf
        s_mf.state = 0 and -- ONLINE
        has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
    group by s_mf.database_id
order by DATABASE_SIZE desc

Open in new window

or just run

EXEC sp_databases

Author Comment

ID: 34165913
My idea is to simple check which SQL "version" is installed like so:

string limit;

if(sqlVersion == "Express 2005") //or ver# ????????????
   limit = "4GB"
else if(sqlVersion == "Express 2008") // or ver# 10.50.1600.1
   limit = "8GB";

Author Comment

ID: 34165933
Just found this, but unsure if it works for ALL Microsoft SQL versions:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 34166218
works for me in 2008 R2

Author Comment

ID: 34172672
Because I only need to know if the DB server is 'Express' or not, the following simple query works fine so far.

Select SERVERPROPERTY('EngineEdition')

         int eEditon = System.Convert.ToInt32(cmd.ExecuteScalar());

         if (eEditon == 4)//
               return true;

Open in new window


Author Closing Comment

ID: 34301164
Thank you all........

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now