601
Chapter 22: Maintaining the Database
22
has been removed from the database, and the database is not confi gured to automatically
shrink, then the following commands may be used to manually shrink the database. The
database can be shrunk while transactions are working in the database.Using auto shrink is not a recommended practice and will defi nitely affect performance because this resource-
intensive operation causes not only index fragmentation but fi le-system level fragmentation as well. For more
detailed information, refer to Paul Randal’s blog: Auto-Shrink - Turn It Off! at http://www.sqlskills.com/
blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspx.For more information on @AutoShrink, see Chapter 20, “Policy Based Management.”DBCC SHRINKDATABASE can shrink the size of the database fi les by performing two
basic steps:- Pack data to the front of the fi le, leaving the empty space at the end of the fi le.
- Remove the empty space at the end of the fi le, reducing the size of the fi le.
You can control these two steps with the following options:
■ (^) The NOTRUNCATE option causes DBCC SHRINKDATABASE to perform only step 1,
packing the database fi le but leaving the fi le size the same.
■ (^) The TRUNCATEONLY option eliminates the empty space at the end of the fi le but
does not fi rst pack the fi le.
■ (^) The target_percent option specifi es the wanted percentage of free space after
the fi le is shrunk. Because autogrowth can be an expensive operation, leaving some
free space is a useful strategy. If the desired free space percentage is larger than
the current amount of free space, this option does not increase the size of the fi le.
The following command shrinks the AdventureWorks2012 sample database and leaves 10
percent free space:
DBCC SHRINKDATABASE ('AdventureWorks2012', 10);
Result:
DBCC SHRINKDATABASE: File ID 1 of database ID 9 was skipped
because the file does not have enough free space to reclaim. Cannot
shrink log file 2 (AdventureWorks2012_Log) because requested size
(568KB) is larger than the start of the last logical log file.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
9 2 96 63 96 56
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
c22.indd 601c22.indd 601 7/31/2012 9:24:31 AM7/31/2012 9:24:31 AM
http://www.it-ebooks.info