One topic I see discussed frequently in public discussion forums is how to most effectively shrink a database, why it doesn't always shrink as much as you think it should, and how much overhead is involved. Most of the questions deal with the shrinking of the transaction log, probably because you have to understand many details about internal transaction-log usage to get the log file to shrink physically. I see far fewer questions regarding shrinking data files, even though the same command, DBCC SHRINKFILE, can shrink both data files and log files. Perhaps people don't have as many questions about shrinking data files because on the surface, shrinking a data file seems straightforward and the command doesn't have many subtleties. As long as you have free space in your data files, the SHRINKFILE command usually works for shrinking physical data files.
I think people should ask more questions about shrinking data files because even though the SHRINKFILE command appears to work in most cases, SQL Server is doing a lot of work behind the scenes that you might not be aware of. In addition, some ramifications of running this command can degrade performance. First, let's review how the shrink commands work on data files, then I'll look at some performance issues you need to be aware of.
Shrinking Files
Two commands—DBCC SHRINKDATABASE and DBCC SHRINKFILE—can shrink your database. In this article, I primarily discuss DBCC SHRINKFILE, but many internal mechanisms of the two commands are the same. In particular, with both commands, the shrinking of a data file takes place in two phases.
During the first phase—called the compaction phase—SQL Server attempts to move data from the end of the physical file to empty space near the beginning of the physical file. If it's trying to move indexes, SQL Server moves whole pages at a time, including data from the table itself, if the table has a clustered index. For data from a heap table, SQL Server moves individual rows wherever it can find room closer to the beginning of the file. During the second phase, SQL Server releases unused space at the end of the file back to the OS.
The syntax for both shrink commands contains two options that you can use if you want to skip one of the two phases. If you issue the command with the NO_TRUNCATE option, SQL Server skips the second step and performs only the compaction phase. If you specify TRUNCATE_ONLY, SQL Server skips the compaction and only removes any space that already exists at the end of the physical file.
Now let's look at three major trouble spots to watch out for when you're shrinking a data file. The first problem is with the amount of time and system resources required to perform all the data movement. SQL Server uses a lot of CPU resources to determine where space is available in the file to move data to, and it uses a lot of IO resources for reading and writing data into new physical locations. In addition, you should be aware that if a table has no clustered indexes, the nonclustered indexes use an address within the data file to keep track of the data in the table. So, if SQL Server has to move data from a heap table, it must adjust any nonclustered indexes for every row that it moved so that they reference the new physical location of the data. This increases the use of system resources considerably.
The second problem is the growth of the transaction log. SQL Server logs every data-movement operation, every page and extent allocation or deallocation, and every change to every index. Not only does this logging add to the use of system resources as part of the first problem, it creates additional problems if the log grows too large and you haven't accounted for all the space it will need. SQL Server MVP Tibor Karaszi has an excellent article on his Web site that demonstrates the enormous amount of log space required for data-file shrinking operations; you can read it at http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
The third problem you run into when shrinking a data file is that the shrinking will likely introduce fragmentation into your tables and indexes. You should be aware that fragmentation isn't always a terrible thing, and many types of operations are unaffected by fragmented data. Microsoft has produced a detailed whitepaper, "Microsoft SQL Server 2000 Index Defragmentation Best Practices" (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp .mspx), that provides information about the different types of fragmentation and the circumstances you need to be concerned about if you have fragmented data.
To see an example of data-file shrinking that causes severe fragmentation, you can run Listing 1's sample script against the Northwind database data file. If you want to be sure to get exactly the same behavior I did, start with a clean installation of the Northwind database. You can find a script to rebuild Northwind in your SQL Server 2000 installation directory. On my computer, the directory is c:\Program Files\Microsoft SQL Server\MSSQL\Install. You can load the file, instnwnd.sql, and run it in Query Analyzer to rebuild and repopulate the Northwind database.
You can then run the code in Listing 1 to create a large copy of the Northwind database's Order Details table (called OrderDetails, with no space in the name), containing about 275,000 rows. The script then copies the big table into one called BigDetails, adding an identity column to provide a primary key. Next, the script copies BigDetails into BigDetails2 and builds a clustered index on the identity column. Finally, it drops OrderDetails and BigDetails, leaving a lot of empty space in the data file.
Prev. page
[1]
2
next page 
