Maintenance Plan Wizard (Define Shrink Database Task Page)

  • Article

Use the Define Shrink Database Task page to create a task that attempts to reduce the size of the selected databases. Use the options below to determine the amount of unused space to remain in the database after the database is shrunk (the larger the percentage, the less the database can shrink). The value is based on the percentage of the actual data in the database. For example, a 100-MB database containing 60 MB of data and 40 MB of free space, with a free space percentage of 50 percent, would result in 60 MB of data and 30 MB of free space (because 50 percent of 60 MB is 30 MB). Only excess space in the database is eliminated. Valid values are from 0 through 100.

This task uses the DBCC SHRINKDATABASE statement, with either the NOTRUNCATE or TRUNCATEONLY option.

Options

  • Databases
    Specify the databases affected by this task.

    • All databases
      Generate a maintenance plan that runs this task against all Microsoft SQL Server databases, except tempdb.
    • All system databases
      Generate a maintenance plan that runs this task against each of the SQL Server system databases except tempdb. No maintenance tasks are run against user-created databases.
    • All user databases
      Generate a maintenance plan that runs this task against all user-created databases. No maintenance tasks are run against the SQL Server system databases.
    • These specific databases
      Generate a maintenance plan that runs this task against only those databases that are selected. At least one database in the list must be selected if this option is chosen.
  • Shrink database when it grows beyond
    Specify the size in megabytes that causes the task to execute.
  • Amount of free space to remain after shrink
    Stop shrinking when free space in database files reaches this size.
  • Retain freed space in database files
    The database is condensed to contiguous pages but the pages are not deallocated, and the database files do not shrink. Use this option if you expect the database to expand again, and you do not want to reallocate space. With this option, the database files do not shrink as much as possible. This uses the NOTRUNCATE option.
  • Return freed space to operating system
    The database is condensed to contiguous pages and the pages are released back to the operating system for use by other programs. This database files shrink as much as possible. This uses the TRUNCATEONLY option.

See Also

Other Resources

DBCC SHRINKDATABASE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

5 December 2005
Changed content:
  • Reworded the description of Retain freed space in database files.