Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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).
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

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)//http://msdn.microsoft.com/en-us/library/ms174396.aspx
               return true;

Open in new window


Author Closing Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

809 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