Free some space on a full Express Edition instance

All we need is an easy explanation of the problem, so here it is.

Using Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64) Express Edition (64-bit)

I have a database that reaches the maximum space (10Gb) every month.

In order to free up some space I do a bacpac, create a new database with the bacpac and shrink it 2 times.

The database goes from 10Gb to 6Gb, and the application using this database gets faster (I think the bacpac refreshes the indexes).

Is there a SQL command to do this? Can I do this in a production database?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Your current process shouldn’t really change application performance (especially with such tiny amount of data) but perhaps your database gets severe index fragmentation and creating the new database results in the indexes being rebuilt. (We wouldn’t be able to determine this without more information such as a before and after execution plan for a specific query that improved due to your process.)

In any case, you can run the same shrink commands on your existing database, no need to export and recreate a brand new database every time. Though usually it’s not recommended to shrink a database, in your use case where you’re bound to only 10 GB per database, I understand the potential need to do so.

In the long term, I’d look to re-architect your application and database to use multiple databases if possible and makes logical sense, as a way to work around the hard cap of 10 GB in SQL Server Express. (E.g. group only certain related tables in one database and other groups of related tables across other databases, or decouple transactionally heavy tables from your less changing ones for another way to slice it.) Cross database querying is always allowed. Or you might find it worth it to purchase a small Standard Edition license eventually.

Furthermore, you might want to consider switching your database to Simple Recovery Model which will reduce the amount of space needed by automatically reclaiming the space used in the Transaction Log. This is likely all the space you’re reclaiming with your current process when you run the shrink command, and therefore will likely remove your need to run the command anymore.

Regarding keeping your indexes defragmented, you should read up on Microsoft’s BOL: Optimize index maintenance to improve query performance and reduce resource consumption and then you can look into Ola Hallangren’s SQL Server Index and Statistics Maintenance which will help you automate that process to ensure your indexes are maintained.

All of this is fine to run in a production environment, but my advise is to always test in a development one first, come up with a game plan that works, then implement it in production.

Method 2

Rebuilding indexes should accomplish a similar effect, but consider making your largest tables Clustered Columnstores, or using Table and Index Compression.

You’ll need to apply the latest service pack to get these features, but you need to do that anyway, as SQL Server 2016 RTM is long out of support.

Note that the columnstore segment cache is limited to 352MB per instance, but this is a bonus, separate from the usual Express 1410MB buffer pool limit. This does not limit the size of your columnstore objects, just the maximum size of the dedicated segment cache.

In summary, Express 2016 SP1+ is limited to:

  • 4 cores
  • Database data size 10GB
  • 1410 MB buffer pool
  • plus 352MB columnstore cache
  • plus 352MB in-memory OLTP (Hekaton) object cache

For more details see:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply