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