You can find more information on the downsides of using shrink, plus some commentary on the new algorithms in SQL Server 2005 on my old MSDNApart from fragmentation at the file-system level and within the log file, it's also possible to have fragmentation within the data files, in the structures that store the table and index data.There are two basic types of fragmentation that can occur within a data file: Internal fragmentation is where there is a lot of empty space in a page.
It's a little more complicated for log files, unfortunately, and you need to consider factors like transaction size (long-running transactions cannot be cleared from the log until they complete) and log backup frequency (since this is what removes the inactive portion of the log).
For more information, see "8 Steps to Better Transaction Log Throughput", a popular blog post on written by my wife, Kimberly Tripp.
For instance, you may want to set a 100GB data file to have a fixed 5GB auto-grow size, rather than, say 10 percent.
This means it will always grow by 5GB, no matter how large the file ends up being, rather than an ever-increasing amount (10GB, 11GB, 12GB, and so on) each time the file gets bigger.
As Figure 1 shows, each page in a database is 8KB in size and has a 96-byte page header; as a result, a page can store roughly 8096 bytes of table or index data (specific table and index internals for data and row structures can be found on my blog at sqlskills.com/blogs/paul in the Inside The Storage Engine category).
Empty space can occur if each table or index record is more than half the size of a page, as then only a single record can be stored per-page.Auto-shrink uses lots of resources and causes performance-dropping fragmentation and so is not a good plan under any circumstances.You should always switch off auto-shrink with: A regular maintenance plan that includes a manual database shrink command is almost as bad.I changed the SQL Server 2005 Books Online entry for shrink to include a warning to this effect.Manual shrinking of individual data and log files, however, can be acceptable under special circumstances.Auto-shrink is the worst offender as it starts every 30 minutes in the background and tries to shrink databases where the auto-shrink database option is set to true.