So as they say, everyday is a school day. Today I learned that my workplace, runs SQL Server Standard edition, where I would have assumed Enterprise was in place. Although in reality shouldn't be surprised!
For some context, we have a very large database that houses our warehouse data. As the database has grown to a large size, it's causing issues with space on the server along with some application performance. So looking at it from my perspective I suggested we archive and purge the PROD database, to house only 18 months data in the PROD environment.
Wrote my scripts and tested them and all fine. I then went to compress the tables I had deleted data from, to find error messages that compression is not available in SQL Server Standard and requires Enterprise edition.
Wondering what my next steps are here? My assumption is that even though I am deleting a lot of data, we won't actually benefit in terms of performance, and space requisition until the tables get compressed.
Shrinking is something I guess I've always shy'd away from, many articles or posts here would advise not to use it.
Wondering, what sort of options do I have here?
Is my assumption correct, in that without compressing, we won't regain space from the trimmed database?
Best Answer
Since 2016sp1 all the compression features have been available in all editions, though that may not help you at all as upgrading your production environments (I'm assuming they run 2014 or earlier?) isn't something to just do on a whim.
If you are deleting the data is compression really what you need? If it is, then to suggest how you might reduce the size of the remaining data we'd need to know a lot more about that data and its use.
Shrinking is usually not what you want to do so you are correct to be cautious - if the data could grow back to use the space again then you might as well leave it allocated to the DB as this will avoid the future (potentially performance harming if they happen at an inconvenient time) growth operations, and the shrink process can cause significant fragmentation especially if (as I've seen done) the data files are shrunk on a regular basis.
But if you have reduced the data size of the database by archiving/deleting a large amount of data and you don't expect the amount of data to grow back to close to its original size any time soon, a one-off shrink should not have significantly concerning side-effects. But shrink to how large the data is expected to be after a reasonable length of time, don't shrink down to the smallest the current data will fit in.