3

I need to know if adding another data file in a database (let say MYDB) in express edition will have another 10 GB size or 10 GB will be the total size of all the data files we add in a database. Actually I am using SQL Server 2014 Express edition and we know that DB size is limited to 10 GB and thus I have to archive the database once it reached to 10 Gb which I don't want.

My purpose is to ask this question is that once the database size reached to 10 GB, can adding another data file help me to avoid archiving the database and continue with the same database?

Improve this question
1
6

The size limit on Express Edition is for the database size, not the data file size.

You can have ten 1GB files, or one 10GB file.

If your data is larger than 10GB, you'll need to move some data to a different database.

Can you get around this?

Sure.

Say your database had one table that was 5GB, and the sum size of every other table was 5GB, putting you at the size limit.

  1. Create a second database, and move your 5GB table into that database.
  2. Drop the original table in the original database.
  3. Create a synonym in the original database, with the name of the original table, and point it at the new table in the new database.
  4. Existing code will "just work" and you'll have expanded your capacity beyond 10GB.
Improve this answer
5
  • You could also leverage cross-database queries in conjunction with, or instead of, synonyms. – LowlyDBA - John M Jan 30 '20 at 15:08
  • I think you're tied up in semantics. Whether it's a synonym pointing at an object in another database, or you're referencing Db.schema.object, it's still a cross database query. In fact, both will have the same execution plan and do the same "stuff". The synonym is just an abstraction to make the reference easier. – AMtwo Jan 30 '20 at 23:46
  • Nope, just suggesting that you could do it with or without the synonyms is all. – LowlyDBA - John M Jan 31 '20 at 1:41
  • Synonyms provide the benefit to make the change without changing code. Moving the table without replacing it with a synonym will require a code change. That's not always possible (ex with a third party app), and never seamless. – AMtwo Jan 31 '20 at 14:14
  • Correct. They also can be problematic with certain situations, like when using Entity Framework. Hence my note that they are not required but merely an option to use for cross database queries. – LowlyDBA - John M Jan 31 '20 at 15:08
3

Regardless of how many files are in a database for Express, the total database size cannot exceed 10GB.

There are plenty of workarounds and strategies to help mitigate this, however.

Some options are:

  • Ensuring data is properly normalized to reduce the size of tables
  • Enabling compression (if you upgrade past 2014 - which you should for a lot of reasons!)
  • Horizontally or vertically partitioning tables across multiple databases
  • Using the smallest data types possible

I've gone more in-depth on the above topics in an article I wrote on on SQL Server Express size limitation strategies.

Improve this answer
1
  • Got it. Thanks for the answer. Appreciated – Chandan Feb 2 '20 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.