We all know that there is a limit in SQL Server Express Edition with respect to the size of the database. There have been many confusions around this area. This blog post is meant to come clear of those doubts – by no means this is not exhaustive of all. I have kept them in Q&A format and would add more.
- What is the size limit you are talking about in SQL Server Express Edition?
That is a function of the version of SQL Server:
SQL 2000 Desktop Engine (MSDE) has limit of 2 GB data file(s) size.
SQL 2000 Desktop Engine (MSDE) has limit of 2 GB data file(s) size.
SQL 2005 Express Edition has limit of 4 GB data file(s) size.
SQL 2008, SQL 2008 R2, SQL 2012, SQL 2014 and SQL 2016 Express Edition has limit of 10 GB data file(s) size.
- What is the error I would get if I cross that the limits of data sizes mentioned above?
You are likely to face errors in that scenario. Here are two common errors for reference:
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database
Size in the error message would depend on limit of that SQL Version of Express. The above message is from SQL 2008 R2 till SQL 2014.
In ERRORLOG, you would see errors as below when limit is reached.
Could not allocate space for object ‘TableName’.’IndexName’ in database ‘DatabaseName’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Could not allocate space for object ‘TableName’.’IndexName’ in database ‘DatabaseName’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
- Is this limit on size for per instance or per database?
The size limit is per database so if the database is composed of multiple data files, then the limit is across all data files in the single database.
- Can I have 2 databases of 10 GB each in SQL Server 2014?
Yes. As explained in #3 its per database data file limit.
- Does this limit include Transaction Log (LDF) file?
No. It’s only for data files.
- Does this limit include File stream file container?
No. It’s only for data files.
- Is there any workaround to increase the limit?
There is none to increase the file size limitation. Filestream is not part of the limit so is possible move the blob data file stream.
- Will my database be unusable?
No. Adding new data, which needs file size increase would fail with the error mentioned above. SELECT can still run on the database.
- What if I want to upgrade to full version of SQL from SQL Express?
That can be done using “Edition Upgrade” Below talks about EVAL edition but same steps can be applied for SQL Express upgrade also.
- What else can be done?
Delete unwanted data and shrink the data file.
.gif)
0 Comments