4

I've got a database which has an 850GB MDF file. Over the course of 20 months a logging mechanism in the application created a variety of huge tables. The maintenance script which was only supposed to retain a weeks data at any one time wasn't executing properly (it is now) so we had to manually clean things up.

We actually cleared about 600GB worth of data. I want to free up some of this space as the drive it is on is near limit. The data file will never grow this large again so it's just wasted space.

I was planning to use SHRINK-FILE to deal with this. I have tested on a clone of the database it takes around 3 hours. There doesn't seem to be any performance degradation on the database. Yeah, the indexes are heavily fragmented afterwards but I can sort those.

My question is this:

The application needs to remain 'up' throughout this process as it's critical. I understand that the SHRINKFILE operation is a fully online one, or am I wrong?

The database has a large amount of write activity occurring pretty much every second.

Will the increased I/O caused by the SHRINKFILE operation affect these writes?

Turning this on its head, will the continuous writes affect the SHRINKFILE operation, i.e. make it slower to complete?

Finally, is there a better way to do this?

Update

Just for anyone reading this - I ran SHRINKFILE and monitored I/O and CPU. There was a slight rise in both but no latency issues at all with my high-writes-per-second database. It remained up, stable and trouble-free throughout the 1.5 hour SHRINK operation.

Indexes were horribly fragmented afterwards which gave the reporting side of the application a little grief (graphs were slow to render on the client application) but an Online index rebuild later and everything was back to normal.

6

It's impossible for us to know if it will affect the application, and it will be extremely difficult for you to determine this either. Even with a 100% like-for-like system and distributed replay clients, you would not notice the millisecond impact the disk IO has, and you would not be able to tell that the application itself is sensitive to that.

The only certainty is if the vendor themselves provides you a solution to prove it one way or the other (i.e. the application itself can do its own load testing) and IMHO very few vendors have such a thing.

Now onto the rest.

Shrink file on a data file is doing a lot IO and this could affect an extremely latency sensitive application - but I've never seen it. If your backups aren't killing you then it's fairly unlikely that this will kill you either. You'd have to already be living on a knife edge of tolerances.

As a DBA you should always cover yourself by letting people know what's going on (either formally through a change ticket or informally through other means). Let them know there's no known or expected impact but that you're available to stop it on a dime in the off chance anything does occur. Shrink file does stop immediately if you cancel it.

But otherwise for me it's a business as usual change with no impact. I'm not in finance or health care so your standards may be higher.

Here's what you do need to know though:

  • You'll be generating a lot of log records. This means you better be in simple mode, or full mode with very frequent (every few minute) log backups, to ensure you don't start filling your disk.

  • If you're in full mode you'll have a massive set of DIFF backups (if you do them) up until the next full backup.

  • On the weekend, or whenever your index maintenance window is, you're going to hit the same case of massive log backups and DIFF backups. This might be a problem if your maintenance does rebuilds and not just reorganisation; rebuilds could put you outside of your maintenance window, will take a massive amount of log or tempdb space while running, and blow up to hundreds of GB. If they're just doing reorganisation though (i.e. no outage) then you'll just see the large log and DIFF backups.

Now all of those latter things definitely can cause outages. But if you have plenty of backup space and disk space for your data and log files, and every-few-minute log backups, and are only doing reorgs, and have sensible small non-percentage based log growth sizes set, then you're unlikely to run into any trouble.

  • I appreciate your answer, thanks. This makes sense. You're right we cannot fully test the behavior of the system during a shrink. To add further complexity, the application has its own index maintenance mechanism which fires every 30 minutes, seems overkill to me, but its presumably to ensure all indexes are always fully optimized given the sheer amount of write activity. So, another worry for me is that it will suddenly find its very large indexes heavily fragmented and I am not sure how that will manifest itself... – Molenpad Sep 12 '16 at 13:00
3

Shrink would be highly IO intensive as it's moving data from the end of the file to the beginning (and that's where the intense fragmentation comes from). It is a re-startable process though and can be cancelled at any time. Any data movement that occurs during a shrink will remain intact should you cancel the process.

You might want to try SHRINKFILE TRUNCATEONLY first - Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.

  • Definitely thought about TRUNCATEONLY, but we have a requirement to specify a targetsize, which is not, I do not believe, possible with TRUNCATEONLY? – Molenpad Sep 12 '16 at 12:56
1

If there's enough space still left, the best approach may be to 1) create a new filegroup, 2) rebuild the index(es) for the table(s) into the new filegroup with the ONLINE=ON switch, and then 3) run a SHRINKFILE operation to reduce the footprint of the original filegroup. The rebuild operation is multi-threaded while SHRINKFILE last time I checked is a single-threaded process and inherently slower. You also mitigate the fragmentation side-effect by doing the rebuild up front. The catch is having enough space, but if you can rebuild enough indexes into the new FG before the volume capacity is reached, you'll have an overall faster SHRINKFILE operation to reclaim the space from the original FG.

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.