asked
Are there any benefits of having a smaller database file?
Say you have a genuine reason for shrinking a database (re-claiming space after applying retention)... With exception to the obvious available disk space reclaim, are there any underlying benefits of having your database smaller in size on disk?
From a SQL perspective, recover-ability would be one. If the file is large (meaning you don't shrink) upon restore SQL will still carve out that space (even empty) so it could slow down your restore times. You have to weigh that against the likelihood that the file will grow again at some point after you shrink it. I tend to NOT shrink things unless absolutely necessary. This is also where things like instant file initialization (IFI) comes into play. Also a fast disk I/O subsystem would help to mitigate that issue. From an sysadmin perspective, I could maybe see something like SAN block replication where a large database would be an issue. There could be other 3rd party stuff in this realm that might not like a large database. NOTE: I'm not a sysadmin. There is a time and place to shrink just like with everything else. ;-) Hope that helps!
We're looking at 400GB free space on a 1TB database, largest index being 100GB. Shrinking to get out of the disk space alert zone rather than increasing the volume was the plan. It'll take years (I say loosely), before we see a data growth event at the current rate. Thoughts were then led to confirming potential gains of shrinking further, which is the reason I asked. I appreciate your comments from both perspectives. Thanks John :)
Shrinking a 1TB database is going to be cumbersome no matter how you slice it. With the given amount of free space and that you don't think you'll consume that much space for some time (thus causing a growth event), I might consider just adding a small amount of disk to the drive and then adjust the alert. I'm assuming of course that it's the only file on the drive. If you've got other DB's sharing the drive, then that might be another story. ;-)
18 People are following this question.
