|
Home > Archive > MS SQL Server > December 2006 > Why do sp_helpdb return maxsize Unlimited for Express Edition
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Why do sp_helpdb return maxsize Unlimited for Express Edition
|
|
| Kjell Arne Johansen 2006-12-12, 7:12 pm |
| Hi
I'm trying to find out how I can find the maximum size for a database.
I'm running exec sp_helpdb in SQL Server Express Management Studio to find
the maximum size of databases, SQL Server Express databases and
Standard/Enterprise editons also.
But exec sp_helpdb returns "Unlimited" max size also for SQL Server Express
databases which can have a max size of 4.0GB.
Are there any other way to find the maximum size of a database, also SQL
Server Express databases.
Thank you
Regards
Kjell Arne Johansen
| |
| John Bell 2006-12-12, 7:12 pm |
| Hi
The part that produces the file sizes is a call to sp_helpfile.
I don't believe SQL Express has a different version of this procedure
compare to others.
In other version of SQL Server Unlimited as a maximum size implies it is
limited by the file system. With SQL Express the value of Unlimited is the
minimum value of the space available on the filesystem and 4GB. Therefore the
maximum file size can still be less than 4GB so saying 4GB is the maximum
size may not be correct!
Without going and finding out the disc space available (which could slow
things down alot), you would have to use a short generic word/description
instead.
Maybe something like 'Default' or 'System' would be better, but I guess
there will be people that don't like them either!
John
"Kjell Arne Johansen" wrote:
> Hi
>
> I'm trying to find out how I can find the maximum size for a database.
> I'm running exec sp_helpdb in SQL Server Express Management Studio to find
> the maximum size of databases, SQL Server Express databases and
> Standard/Enterprise editons also.
> But exec sp_helpdb returns "Unlimited" max size also for SQL Server Express
> databases which can have a max size of 4.0GB.
>
> Are there any other way to find the maximum size of a database, also SQL
> Server Express databases.
>
> Thank you
>
> Regards
> Kjell Arne Johansen
| |
| Razvan Socol 2006-12-12, 7:12 pm |
| The maximum size of a particular file in a database (as returned by
sp_helpdb) is the one configured using something like this:
ALTER DATABASE dbname MODIFY FILE NAME = filename, MAXSIZE = size
This size has nothing to do with the system capacities of various
editions of SQL Server.
You can find out the edition of SQL Server using:
SELECT SERVERPROPERTY('Edit
ion')
or
SELECT SERVERPROPERTY('Engi
neEdition')
Based on this information, you can deduce the maximum database size
using some hard-coded values.
Razvan
Kjell Arne Johansen wrote:
> Hi
>
> I'm trying to find out how I can find the maximum size for a database.
> I'm running exec sp_helpdb in SQL Server Express Management Studio to find
> the maximum size of databases, SQL Server Express databases and
> Standard/Enterprise editons also.
> But exec sp_helpdb returns "Unlimited" max size also for SQL Server Express
> databases which can have a max size of 4.0GB.
>
> Are there any other way to find the maximum size of a database, also SQL
> Server Express databases.
>
> Thank you
>
> Regards
> Kjell Arne Johansen
| |
| Kjell Arne Johansen 2006-12-13, 5:18 am |
| Hi
Thank You for your answer.
Regards
Kjell Arne
"Razvan Socol" wrote:
> The maximum size of a particular file in a database (as returned by
> sp_helpdb) is the one configured using something like this:
> ALTER DATABASE dbname MODIFY FILE NAME = filename, MAXSIZE = size
> This size has nothing to do with the system capacities of various
> editions of SQL Server.
>
> You can find out the edition of SQL Server using:
> SELECT SERVERPROPERTY('Edit
ion')
> or
> SELECT SERVERPROPERTY('Engi
neEdition')
> Based on this information, you can deduce the maximum database size
> using some hard-coded values.
>
> Razvan
>
> Kjell Arne Johansen wrote:
>
>
| |
| Kjell Arne Johansen 2006-12-13, 5:18 am |
| Hi
Thank You for your answer.
Regards
Kjell Arne
"John Bell" wrote:
[color=darkred]
> Hi
>
> The part that produces the file sizes is a call to sp_helpfile.
>
> I don't believe SQL Express has a different version of this procedure
> compare to others.
>
> In other version of SQL Server Unlimited as a maximum size implies it is
> limited by the file system. With SQL Express the value of Unlimited is the
> minimum value of the space available on the filesystem and 4GB. Therefore the
> maximum file size can still be less than 4GB so saying 4GB is the maximum
> size may not be correct!
>
> Without going and finding out the disc space available (which could slow
> things down alot), you would have to use a short generic word/description
> instead.
> Maybe something like 'Default' or 'System' would be better, but I guess
> there will be people that don't like them either!
>
> John
>
>
> "Kjell Arne Johansen" wrote:
>
|
|
|
|
|