In a previous post, we mentioned that shrinking a database can cause fragmentation, and fragmentation can cause Workforce Central to run slowly. Paul Randall discussed this issue in a blog post (http://www.sqlskills.com/blogs/paul/post/Why-you-should-not-shrink-your-data-files.aspx) almost a year ago. But as we talk to DBAs who are running Workforce Central, we find that many are still shrinking database files on a regular basis.
Over the years, we've heard many explanations for regularly shrinking database files—with lack of storage space being the leading reason. Of course, you usually have to make do with what you are given, and often you have to play the "disk storage game" to meet the needs of multiple databases on a single disk array. A tip—save storage space by making sure that your index maintenance plan specifies that the sorting occurs in tempdb to keep your files from growing unnecessarily. Here is how you alter your indexes to do that:
ALTER INDEX index_name ON table_schema.table_name REBUILD WITH (SORT_IN_TEMPDB = ON)
More information about this subject can be found at http://msdn.microsoft.com/en-us/library/ms188281.aspx.
DBAs also say that they feel the need to clean up their files after deleting data. We recommend against this unless you really, really need to reclaim the storage space. Give your database files as much space as they will need in the first place, and then leave them alone. An exception—if you delete a lot of data and you know for sure you will never need that space again, you can shrink the database files.
When you shrink a data file, SQL Server first moves pages toward the beginning of the file, freeing up space at the end—and it is this extra space that is removed to shrink the file. Why is this so bad? Well, the shrink process simply moves pages without regard for the logical ordering of the pages. As we discussed in an earlier blog post on fragmentation, when index or data pages are out of order, the disk head has to work much harder to read those pages, and the amount of data that can be read in a single sequential read is reduced. The read-ahead process can only read blocks of pages that are in order. If none of the pages are in order, it is unlikely that you can achieve large read-aheads. (We will talk more about sequential reads and the read-ahead process in an upcoming post.)