• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

Get DB Maximum Capacity Query?

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.
0
pointeman
Asked:
pointeman
3 Solutions
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
http://msdn.microsoft.com/en-us/library/ms143432.aspx

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 !
USE DATABASE_NAME
GO
EXEC sp_spaceused
GO

Open in new window


hope that helps
0
 
Anthony PerkinsCommented:
And it should be added that the MSDE/Express Editions have limitations of 2GB, 4GB and 10GB, depending on the version.
0
 
subhashpuniaCommented:
For SQL Server 2008 the max size limit of database is 524,272 terabytes (TB).
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Scott PletcherSenior DBACommented:
I would think he means the largest existing db on the instance.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
OK, then run that, to get list of DBs along with Size, in DESC order
use master
go
 select
        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
                                    end)
    from
        sys.master_files s_mf
    where
        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
0
 
pointemanAuthor Commented:
My idea is to simple check which SQL "version" is installed like so:

C#
string limit;

if(sqlVersion == "Express 2005") //or ver# ????????????
}
   limit = "4GB"
{
else if(sqlVersion == "Express 2008") // or ver# 10.50.1600.1
{
   limit = "8GB";
}
0
 
pointemanAuthor Commented:
Just found this, but unsure if it works for ALL Microsoft SQL versions:

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

0
 
Jason Yousef, MSSr. BI DeveloperCommented:
works for me in 2008 R2
0
 
pointemanAuthor Commented:
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

0
 
pointemanAuthor Commented:
Thank you all........
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now