Is there a way to find max allowed size of the database? 1

[markros]

I am wondering if there is a way to figure out the maximum allowed size for the database using a query in SQL Server? As we know, the limit is 4GB for SQL Server 2005-2008 Express and 10GB for SQL Server 2008 R2 Express. I don't want to hard-code these limits, I want to be able to run a query to determine these limits.

We have a method that checks if the database is approaching the limit (if it's an Express version) and warn about it if it does.

Thanks a lot in advance.

PluralSight Learning Library

 

[TonHu]

Guess you'll have to translate the server revision level to a max. available size. There's a SP to get that, never used it, so better google for it.

 

[markros]

Basically, as I found from discussion on a different forum, the max database limit is not exposed by SQL Server, so I still have to hard-code the values based on the edition. The limits are available in MSDN, just not in one place, so I hope to write a WiKi article in TechNet to consolidate this info together.

PluralSight Learning Library

 

[markros]

What I'm trying to figure out right now and may be someone can help, as how to find the database size only (as the max size limitation is for DB only). Also, I'd like some clarification - DB only - does it mean excluding the log only or excluding ndf file (indexes) as well?

Thanks in advance for the answer.

PluralSight Learning Library

 

[gmmastros]

According to:

http://msdn.microsoft.com/en-us/library/ms345154(v=sql.90).aspx

The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server.

So, looks like the mdf + ndf is counted towards database size but not the ldf.

I would suggest that you take a look at the code for sp_helpfile so that you can write your own query.

[tt]sp_helptext 'sp_helpfile'[/tt]

There appears to be a status column in the sysfiles table to determine if the file is a log file.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Thanks, based on the discussion on another site this is what I wrote

DECLARE @MaxSize INT, @MaxSizeDesc varchar(10)
  SELECT @MaxSize = CASE WHEN SERVERPROPERTY('ProductVersion') > '10.5' THEN 10 * 1024 * 1024 -- 10GB for SQL Server 2008 R2 Express 
			ELSE 4 * 1024 * 1024 END,
		 @MaxSizeDesc = CASE WHEN SERVERPROPERTY('ProductVersion') > '10.5' THEN '10 GB' 
			ELSE '4 GB' END

  SELECT SUM(SIZE*8) AS SizeKB, @MaxSize AS MaxSize, @MaxSizeDesc as MaxSizeDesc
   FROM sys.master_files
    WHERE DB_NAME(database_id) = 'DbName here' AND type = 0

Do you think it's a correct query?

PluralSight Learning Library

 

[gmmastros]

It looks pretty good. The only thing I would question (because I don't know) is with reagards to the "type = 0" part. According to the documentation:

0 = Rows. (Includes files of full-text catalogs that are upgraded to or created in SQL Server 2008.)

1 = Log

2 = FILESTREAM

3 = Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

4 = Full-text (Full-text catalogs earlier than SQL Server 2008; full-text catalogs that are upgraded to or created in SQL Server 2008 will report a file type 0.)

I don't know if FILESTREAM and Full-Text are included in the 4/10 gig limit.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Mike Lewis]

But if it's the full product (not an Express version), won't your code incorrectly return 10 GB?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[fredericofonseca]

after you do all your math's, take in consideration that one way to get around most of the situations where you would get that limitation is to split the tables over several databases, as the limit is per DATABASE not per instance.
I know of a software house that had over 100 databases - each one with 1 table. any client of them that has a single table going near the limit is big enough to have money to pay for a full blown enterprise version.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

Frederico,

I did something similar once where I have 100 db's totaling 60 gigs on a single instance. Each DB had the same tables (but not the same data). I then had a 101st DB the had views that combined all the tables from all the DB's for querying purposes. It actually worked a lot better than I thought it would.

The downside is that express is limited to using 1 gig of RAM. According to the documentation:

Lack of enterprise features support
Limited to one CPU
One GB memory limit for the buffer pool
Databases have a 4 GB maximum size

The 1 CPU and 1 gig mem limit is likely to prevent most people from trying to hack the limits.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, I understand that and yes, the code is only for Express Edition.

Anyway, I'm having the problem with the code above and can not figure this out, so I need another reliable way to get the same info by not referencing master (I think my problem is related with permissions).

This code works OK in SQL Server, but when I call it from the application, it returns null as the SizeKB.

Can you help with a different query to get the same result?

PluralSight Learning Library

 

[gmmastros]

This code works OK in SQL Server, but when I call it from the application, it returns null as the SizeKB.

This happened for me the first time I ran it. I didn't notice 'dbName here' part. Once I changed it, everything was fine.

As a first step, try this where clause:

WHERE database_id = db_id() AND type = 0

This where clause will only return data for the DB you are currently logged in to.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

This query returns the same information:

DECLARE @MaxSize INT, @MaxSizeDesc varchar(10)
  SELECT @MaxSize = CASE WHEN SERVERPROPERTY('ProductVersion') > '10.5' THEN 10 * 1024 * 1024 -- 10GB for SQL Server 2008 R2 Express 
            ELSE 4 * 1024 * 1024 END,
         @MaxSizeDesc = CASE WHEN SERVERPROPERTY('ProductVersion') > '10.5' THEN '10 GB' 
            ELSE '4 GB' END

  SELECT SUM(SIZE*8) AS SizeKB, @MaxSize AS MaxSize, @MaxSizeDesc as MaxSizeDesc
   FROM sys.database_files
    WHERE type = 0

Permissions on sys.database_files is "easier" than sys.master_files, but again... you're limited to the currently logged in DB.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Thanks again, in the meantime I got it working with the

  DECLARE @MaxSize INT, @MaxSizeDesc varchar(10)
  SELECT @MaxSize = CASE WHEN SERVERPROPERTY('ProductVersion') > '10.5' THEN 10 * 1024 * 1024 -- 10GB for SQL Server 2008 R2 Express 
			ELSE 4 * 1024 * 1024 END,
		 @MaxSizeDesc = CASE WHEN SERVERPROPERTY('ProductVersion') > '10.5' THEN '10 GB' 
			ELSE '4 GB' END

  
   SELECT SUM(CONVERT (BIGINT, SIZE) * 8) AS SizeKB,
    @MaxSize AS MaxSize, @MaxSizeDesc AS MaxSizeDesc
	FROM sysfiles F WHERE status & 0x40 <> 0x40 -- Exclude log files

based on your earlier response. I guess I keep it as is unless you think your version is better.


PluralSight Learning Library

 

[markros]

Good catch with your prior response also. May be that was the problem.

Anyway, whole evening yesterday and most of the date today is spent on this simple issue. I have to move on.

PluralSight Learning Library

 

[markros]

Looks like I also missed a lot of responses - now I read all of them.

We're basically dropping support for SQL Express as of July 2012. But for now we have the code in one of our applications to show warning once DB is reaching the critical size in case they are using Express edition. So, yesterday working on something else I noticed the code in that method was not correct (wrong limits). So, I sent an e-mail to my manager (and colleagues) and as a result got a task to fix it. I then spent a lot of time researching this problem and playing...



PluralSight Learning Library