středa 26. února 2014

MS SQL Server Express - limit velikosti

Jednotlivé verze MS SQL Server mají tyto limity na velikost databáze:

  • 2000 Desktop - 2 GB
  • 2005 Express - 4 GB
  • 2008 Express - 4 GB
  • 2008 R2 Express - 10 GB
  • 2012 Express - 10 GB
Tímto SQL příkazem lze zjistiti verzi, edici ... a další věci ohledně instalované databáze.

select
 SQL_Server_Version = @@version,
 SQL_Server_Edition = serverproperty('Edition'),
 SQL_Server_Name = serverproperty('ServerName'),
 Database_Name = a.Name,
 Maximum_Database_Size_MB = case when serverproperty('EngineEdition') in (1, 4) then case when cast(serverproperty('ProductVersion') as nvarchar(128)) like '8.%' then '2048 MB' when cast(serverproperty('ProductVersion') as nvarchar(128)) like '9.%' then '4096 MB' when cast(serverproperty('ProductVersion') as nvarchar(128)) like '10.0%' then '4096 MB' else '10240 MB' end else 'Effectively no limit' end,
 Data_Size_MB = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
 Available_Growth_MB = case when serverproperty('EngineEdition') in (1, 4) then case when cast(serverproperty('ProductVersion') as nvarchar(128)) like '8.%' then 2048 when cast(serverproperty('ProductVersion') as nvarchar(128)) like '9.%' then 4096 when cast(serverproperty('ProductVersion') as nvarchar(128)) like '10.0%' then 4096 else 10240 end end - convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))
from
 sys.sysfiles a
where
 a.name not like '%_log';


  • SQL_Server_Version - a verbose description of the version, edition, and other properties of your SQL Server environment
  • SQL_Server_Edition - the edition of SQL Server
  • SQL_Server_Name - the name (including instance) of SQL Server
  • Database_Name - the name of your database
  • Maximum_Database_Size_MB - for Express editions, the upper limit of the size of the database, measured in Megabytes; for Standard editions, "Effectively no limit"
  • Data_Size_MB - the amount of data stored in the database, measured in Megabytes. This is the critical number.  If this number ever equals the value shown for the Maximum_Database_Size_MB, the Shelby v.5 software will start throwing errors whenever new values are entered because there will be no room to store them in the database.
  • Available_Growth_MB - the difference between the maximum database size and the current data size; when this reaches zero, trouble will begin.

Žádné komentáře:

Okomentovat