2

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?

|improve this question
  • 2
    You wouldn't regain disk free space from compressing tables and indexes without shrinking stuff, anyway. You'd just have a bunch of empty space in your data file. – Erik Darling Nov 14 '17 at 14:40
  • That isn't to say you can't get some performance benefits without shrinking (if you have queries doing scans you'll have less records to scan, for example), just that you won't get disk space benefits without shrinking. – LowlyDBA Nov 14 '17 at 14:42
  • All types of compression are available in SQL Server Standard Edition as of SQL Server 2016 SP1. See docs.microsoft.com/en-us/sql/sql-server/… – David Browne - Microsoft Nov 14 '17 at 14:54
  • 1
    @DavidBrowne-Microsoft that's very optimistic of you :) – Erik Darling Nov 14 '17 at 14:57
  • One other option that I haven't seen mentioned here--to regain space, you can also create new files and move tables to them. This will require additional space during the process, but when you've moved all tables, the new files will be the minimal size, and then you can shrink the old file(s). This will avoid the task of shrinking files that have lots of data in them. Lots of details to consider and I'm not suggesting this is the best option even 1% of the time, but it's an option. – Tony Hinkle Nov 14 '17 at 16:09
4

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.

I then went to compress the tables I had deleted data from

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 something I guess I've always shy'd away from,

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.

|improve this answer
  • Hi David. This work is being performed in conjunction with a third party vendor, and they are the ones indicating a shrink or compression should take place? Personally, I know from experience that the data will likely get back to that size in about five years, requiring the task to be completed again. If the script for clearing down will actually reduce the DB size without a compression or shrink, than that would actually suffice for my requirements. – Daniel Nov 14 '17 at 15:00
  • I'm maybe under the illusion (from other colleagues) that just deleting data, will still leave the DB size as is, and that the compression is required to actually reduce the overall DB size, is this correct? *For context, I'm not a DBA per say, just get allocated a lot of DBA work in my role :) – Daniel Nov 14 '17 at 15:02
  • Compressing data will not free disk space allocated to the database any more than deleting data will - you may end up with more free space within the database files but the files themselves will remain their current size. Shrinking is the only way to release the filesystem space taken by database files (well, except migrating the data to another filegroup and deleting the original files once unused, but that will use more space, perhaps double, during the process). – David Spillett Nov 14 '17 at 15:13

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.