0

We have a reporting SQL (2014 R2 Enterprise) system with a Reporting and Staging DB on it. The reporting DB is pre-provisioned at 4 TB across 8 data files. Currently it contains just shy of 1 TB of data and growth is marginally slow.

We need to reclaim 2 TB of that pre-allocated space on the SAN...

Since the data files are pre-grown, I assume I need to just change the file initial size and then perform a shrinkdb operation. I know the concerns and issues involved in a shrinkdb, and I just reviewed the article written by @BrentOzar (https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/) about it.

Given the pre-allocated file size versus the actual usage, should I be less worried about the fragmentation consequences?

On a side note: I love the tag descriptor when entering 'Shrink' as a tag...

  • 1
    Is the data spread across all 8 files or is it all in 1 of them? – CasualFisher Aug 9 '18 at 20:31
  • 1
    I would definitely shrink the files instead of the database. Regarding fragmentation, we don't know how fragmented your indexes are currently, or if fragmentation even matters in your environment. Brent has a few blogs and videos about this too. Here's one and here is a post about internal and external fragmentation with notes on fill factor, etc. – scsimon Aug 9 '18 at 20:56
  • @sqldavedb it's spread out. each file is ~534gb with 126gb of space used. (76% free) – Wes Aug 9 '18 at 21:37
  • I should note, it's all on flash storage. – Wes Aug 9 '18 at 21:38
  • 1
    Have you tried shrink file with truncate only? Will avoid any page movement – CasualFisher Aug 10 '18 at 19:04
0

In the end, as some have suggested, our only option was to incrementally shrink files w/out truncate only. The cost/benefit didn't really work out, so we are living with what we have.

Thank you all for the help and suggestions.

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.