Sql-server – Regarding Sql server 2014 express edition

sql serversql server 2014sql-server-express

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?

Best Answer

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.
Related Question