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?
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.
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:
For more details see:
