1

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...

Improve this question
6
  • 1
    Is the data spread across all 8 files or is it all in 1 of them?
    – CasualFisher
    Aug 9, 2018 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.
    – S3S
    Aug 9, 2018 at 20:56
  • @sqldavedb it's spread out. each file is ~534gb with 126gb of space used. (76% free)
    – Wes
    Aug 9, 2018 at 21:37
  • I should note, it's all on flash storage.
    – Wes
    Aug 9, 2018 at 21:38
  • 1
    Have you tried shrink file with truncate only? Will avoid any page movement
    – CasualFisher
    Aug 10, 2018 at 19:04

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.