The following command will try to shrink the data file to 10MB:
DBCC SHRINKFILE(Northwind, 10)
After running the SHRINKFILE command, I check the fragmentation of BigDetails2 as follows:
DBCC SHOWCONTIG (BigDetails2) WITH TABLERESULTS
I find a logical scan fragmentation of more than 98 percent, meaning that 98 percent of the pages in the table are out of order with respect to the previous page. If I needed to perform an ordered scan on the BigDetails2 table, SQL Server would have to jump all over the disk to access the pages in the desired sequence.
For this example, I knew which table would likely be fragmented; that table was also the only one in Northwind large enough for fragmentation to be a concern. However, in your own databases, you might not know in advance which tables the shrinking will affect. Listings 2 and 3 give you the DBCC SHOWCONTIG output for all user tables in your current database that have a clustered index. The script in Listing 2 builds a table, showcontig_results, that can hold all the output from DBCC SHOWCONTIG; the script in Listing 3 declares a cursor to access all the tables of interest. The code in Listing 3 then steps through the rows of the cursor, running DBCC SHOWCONTIG for each table the cursor accessed. After populating the showcontig_results table, you can search it as you would any other table. For example, to find the names of all tables that have a logical scan fragmentation value of more than 20 percent, you could execute the following query:
SELECT ObjectName FROM showcontig_results
WHERE LogicalFragmentation > 20
Of course, you can change the percentage value to something bigger or smaller than 20, depending on how much fragmentation your system can handle.
Other Shrinking Techniques
In addition to DBCC SHRINKFILE, you can run the command DBCC SHRINKDATABASE, which attempts to shrink all the files in a specified database, including the log files. Because the mechanisms of data-file and log-file shrinking are so different and I usually want to shrink the data files for different reasons than I want to shrink the log files, I prefer not to shrink everything at once. I use DBCC SHRINKFILE and specify which file I want to shrink. When I use DBCC SHRINKFILE, I feel that I have more control over the size I'm shrinking my files to because the second parameter is the number of megabytes I want to shrink the file to.
With DBCC SHRINKDATABASE, the second parameter is a number that tells SQL Server the percentage of free space you want to leave in the database after all the shrinking. You usually can't tell which input value will produce a given amount of shrinkage, or even what value you need to produce any shrinkage at all. The work SQL Server has to do to shrink each file is the same as with DBCC SHRINKFILE, but you have much less specific control over the final size of the database files.
Another drawback of the DBCC SHRINKDATABASE command is that it can't shrink a file smaller than the file's minimum size. The minimum size of a file is the size you specified when creating the file or the last explicit size you set by using a file size–changing operation such as ALTER DATABASE with the MODIFY FILE option or DBCC SHRINKFILE. The ALTER DATABASE with MODIFY FILE command can only make a minimum file size larger, so you need DBCC SHRINKFILE to reduce the file size if you want it to be smaller than the previous minimum size.
Shrink to Fit
A final mechanism for shrinking is the AUTOSHRINK database option. Enabling this option for a database means that every 30 minutes, SQL Server will attempt to execute DBCC SHRINKDATABASE if there's more than 25 percent free space in a database. When the autoshrink operation is initiated, SQL Server shrinks all data files and the log to the smallest size possible. The AUTOSHRINK option is intended only for use on personal or low-use systems where disk space usage is a critical concern. If system performance is more important, you probably don't want to enable the AUTOSHRINK option. And never enable this option on a production database.
Shrinking a file manually, even without the AUTOSHRINK option, should be a very carefully considered operation, in light of all the negative arguments I have presented. DBCC SHRINKFILE might be preferable to DBCC SHRINKDATABASE, but even DBCC SHRINKFILE should be used only in cases of dire necessity. The preferred option is to spend some time planning your expected database growth, so you can create your database files at an appropriate size. Only when you've severely overestimated your space needs and created a database much larger than you required should you ever need to issue one of the shrink commands to shrink your data files.
—Kalen Delaney