Space Management Wizard
Go Up to Using Space Analyst
The Space Management Wizard can create two specialized space management jobs that allow you to proactively eliminate space threats before they affect your database:
Contents
- 1 Reorganize
- 2 Creating or editing a space Management Job
- 2.1 To open the Space Management Wizard
- 2.2 New or Used Task
- 2.3 Type of Space Management Task
- 2.4 Database/Objects to Be Part of the Reorganization or Maintenance Task
- 2.5 Custom Reorganization
- 2.6 Save the Operation
- 2.7 Execution Preferences for a Reorganization Operation
- 2.8 Global Customizations
- 2.9 Specific Object Customization
- 2.10 Threshold Criteria for Object Reorganization
- 2.11 Integrity Check Options
- 2.12 Integrity Check for Specific Database Objects
- 2.13 Unused Space Options
- 2.14 Problem Notification
- 3 Space Analyst - Threshold Options
Reorganize
You can create a "reorganization analyst job" that allows you to specifically select various databases and/or objects and then set reorganization thresholds that determine when a reorganization will occur. Once constructed, you can schedule these jobs to run as often as you'd like. During execution, these jobs will interrogate your chosen database/objects and determine if a reorganization needs to occur by applying your customized reorganization thresholds. If any objects are found to need reorganization, they are dynamically reorganized using any customized options you specify.
You can build space maintenance jobs that will perform functions such as check the validity of your databases and filegroups, repair any found minor corruption, and eliminate unused space in your databases. You can also set the job up to notify you if any problems are found during job execution.
Creating or editing a space Management Job
The Space Management Wizard lets you create space reorganization or maintenance jobs without knowing the code. After creation, jobs can be reused as is, or edited.
To open the Space Management Wizard
- Start the Space Analyst and click the Open Wizard button on the Space Analyst toolbar.
- OR
- Select Open Wizard from the Space Analyst menu.
The sections below display the fields you may find creating or editing a space management job. Be aware that the sections you see, and the order in which they appear in the wizard vary depending on the selections you make and the database platform.
- New or Used Task
- Type of Space Management Task
- Database/Objects to Be Part of the Reorganization or Maintenance Task
- Custom Reorganization
- Save the OperationExecution Preferences for a Reorganization Operation
- Global Customizations
- Specific Object Customization
- Threshold Criteria for Object Reorganization
- Integrity Check Options
- Integrity Check for Specific Database Objects
- Unused Space Options
- Problem Notification
New or Used Task
Here you choose whether to create a new space management task or whether to use a previously saved task. If you want to reuse a previously created job, you can type or browse to find the previously saved task definition file (*.sptsk).
Type of Space Management Task
Required Information | Description |
---|---|
Create standard reorganization job |
Select to build a reorganization job for a database or the objects you specify. |
Create reorganization analyst job |
Select to create and deploy a reorganization job that runs periodically to detect and automatically correct space inefficiencies. |
Create space maintenance job |
Select to build a space maintenance job that validates database structures and performs storage maintenance tasks. |
Database/Objects to Be Part of the Reorganization or Maintenance Task
Choose the databases and/or objects you want to include in the job you are creating. Simply move items from the tree in the right pane to the left pane by clicking the right arrow. You can amend your selections using the left arrow. The selection is limited to entire databases for a space maintenance job.
Custom Reorganization
Required Information | Description |
---|---|
Perform no customizations |
When you choose this option, all default object and database attributes are used. |
Perform global customizations |
This option gives you the chance to define options that will be globally applied to all objects being reorganized. |
Perform specific object customizations |
Here you individualize options for specific objects being reorganized. |
Save the Operation
If you want to save the operation, you can use the default name supplied or type another in the upper panel. If you don’t want to save the operation, remove the default name. Browse to the location where you want to save the file. The job as it was created appears in the read-only preview panel. To make changes, you must go back and change your selections or open the job in an ISQL window prior to execution.
Execution Preferences for a Reorganization Operation
Option | Description |
---|---|
Open the reorganization script in an ISQL editor. |
The reorganization script will be generated and then opened in a new ISQL editing session. |
Perform reorganization now |
The reorganization is run immediately with results presented afterward in an ISQL window. |
Schedule the reorganization script for later execution. |
The reorganization script will be generated and then scheduled to run at a specified date/time. |
Global Customizations
Option | Description |
---|---|
Relocate to file group |
Select to perform an extended alter and move the table(s) to the requested file group using the ON keyword in the table creation DDL. |
Perform sorted reorg using table's clustered index |
Select to rebuild of any table's clustered index. |
Perform online reorganization where possible |
MICROSOFT SQL SERVER ONLY: Select to use the DBCC INDEXDEFRAG command to perform an online rebuild of every table's index(es). |
View reorganization output messages |
Select for the WITH NO_INFOMSGS clause of any DBCC command to not be used. |
Defragment tables in DROP/CREATE fashion |
Select to defragment tables in DROP/CREATE fashion. |
Relocate to file group |
Select for DROP_EXISTING clause instead of doing a drop and then create. |
Fill Factor |
Select to specify a new fill factor for the DBCC DBREINDEX command or a create/drop of an existing index. |
Index Reorganization |
Rebuild:,Drop/Create, or Reorganize |
Perform online reorganization where possible |
Select to use the DBCC INDEXDEFRAG command to perform an online rebuild of every index. |
View reorganization output messages |
Select for the WITH NO_INFOMSGS clause of any DBCC command to not be used. |
Specific Object Customization
Option | Description |
---|---|
Select |
Drop-down list lets you switch between indexes and tables and see the particular objects for each. |
Relocate to file group |
Select a clause instead of doing a drop and then create. |
Fill Factor |
For indexes, select to specify a new fill factor. |
Perform sorted reorganization using table's clustered index |
For tables, select to perform sorted reorganization using table's clustered index. |
Index reorganization |
Rebuild: This is the default. Drop/Create Reorganize |
Perform online reorganization where possible |
Select to perform an online rebuild of every index. |
View reorganization output messages |
Select for the WITH NO_INFOMSGS clause of any DBCC command to not be used. |
Defragment tables in DROP/CREATE fashion |
For tables, select to defragment tables in DROP/CREATE fashion. |
Threshold Criteria for Object Reorganization
Option | Description |
---|---|
Table: Average Page Density |
Default is less than or equal to 50. |
Scan Density Percent |
Default is less than or equal to 0. |
Logical Fragmentation |
Greater than or equal to 25 is the default value. |
Extent Fragmentation |
Default is greater than or equal to 50. |
Forwarded Record Percent |
Default is greater than or equal to 50. |
Index: Average Fragmentation |
Default is greater than or equal to 25. |
Scan Density Percent |
Default is less than or equal to 0. |
Logical Fragmentation |
Greater than or equal to 25 is the default value. |
Extent Fragmentation |
Default is greater than or equal to 50. |
Index Tree Depth |
Default is greater than or equal to 3. |
Average Page Density |
Default is less than or equal to 50. |
- Note: All defaults are editable. Also, all current metrics for these criteria can be found on the Objects tab >Space Diagnostics view. The default thresholds are visible when you choose Threshold Options from the Space Analyst drop-down menu.
Integrity Check Options
Option | Description |
---|---|
Do not perform any integrity checks |
Self-explanatory. |
Check databases |
Checks the database integrity for database(s) you identified earlier. |
Check file groups |
Self-explanatory. |
Check specific database objects |
In a later step you are able to select the particular objects. |
Include index checks for all tables |
This is the default and isn’t actually optional. |
Attempt to repair minor problems |
Enabled as a default for database or database objects integrity checks. You can unselect. |
Include check for database catalogs |
Enabled as a default for database integrity checks. You can unselect. |
Correct data dictionary inaccuracies |
Enabled as a default for database integrity checks. You can unselect. |
Check data purity |
Verifies that columns only have data in the correct ranges, and so on. |
Integrity Check for Specific Database Objects
Here you select objects for the database(s) you have included in this maintenance job.
Unused Space Options
Option | Description |
---|---|
Do not eliminate unused space |
Self-explanatory |
Eliminate unused space for databases |
Select to only truncate space for databases larger than a specific size (in MB). |
Only eliminate space when database is larger than |
Available if you selected Eliminate unused space for databases option. Lets you specify maximum database size in MB. |
Amount of free space to leave for database |
Lets you specify how much free space is to remain after a space truncation. |
Eliminate unused space for specific database tables |
Self-explanatory. |
Problem Notification
Option | Description |
---|---|
No, do not notify me |
Select to not have Space Analyst notify you of any errors. |
Yes, notify me in the following ways. |
Select to have Space Analyst notify you of any errors and then type an e-mail or net send address. |
Space Analyst - Threshold Options
The Space Analyst Reorganization Thresholds Options dialog lets you customize the various reorganization thresholds that can be used to flag storage and data objects that need to be reorganized. The thresholds you specify will apply to the Space Analysis Reports.
Important Notes
None
The table below describes the options and functionality on the Space Analyst Thresholds Options dialog for IBM DB2 for Linux, Unix, and Windows:
Option | Description |
---|---|
Table Reorganization Thresholds |
The table thresholds apply to the displays used in tab 4 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the tablespace analysis report. |
Index Reorganization Thresholds |
The index thresholds apply to the displays used in tab 5 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the tablespace analysis report. |
The table below describes the options and functionality on the Space Analyst Thresholds Options dialog for Oracle:
Option | Description |
---|---|
Tablespace and Datafile Fragmentation Thresholds |
The tablespace and datafile thresholds apply to the tablespace and datafile fragmentation index. The default for each is 50 - meaning, flag any tablespace or datafile whose fragmentation index is less than 50. They are also used for the tablespace analysis report. |
Table Reorganization Thresholds |
The table thresholds apply to the displays used in tab 4 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the tablespace analysis report. |
Index Reorganization Thresholds |
The index thresholds apply to the displays used in tab 5 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the tablespace analysis report. |
The table below describes the options and functionality on the Space Analyst Thresholds Options dialog for Sybase ASE:
Option | Description |
---|---|
Database and Log Space Thresholds |
The database and log space thresholds apply to the database and log fragmentation index. The default for each is 80 - meaning, flag any database or log whose fragmentation index is less than 80. The database and log space thresholds apply to any grid that shows free space percent for those entities. |
Table Reorganization Thresholds |
The table thresholds apply to the displays used in tab 5 of the Space Analyst and the procedures that are run to present the various customized displays. They are also used for the database analysis report. |
Index Reorganization Thresholds |
The index thresholds apply to the displays used in tab 5 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the database analysis report. |
The table below describes the options and functionality on the Space Analyst Thresholds Options dialog for SQL Server:
Option | Description |
---|---|
Database and Log Space Thresholds |
The database and log space thresholds apply to the database and log fragmentation index. The default for each is 80 - meaning, flag any database or log whose fragmentation index is less than 80. The database and log space thresholds apply to any grid that shows free space percent for those entities. |
Table Reorganization Thresholds |
The table thresholds apply to the displays used in tab 5 of the Space Analyst and the procedures that are run to present the various customized displays. They are also used for the database analysis report. |
Index Reorganization Thresholds |
The index thresholds apply to the displays used in tab 5 of the Space Analyst and the queries that are run to present the various customized displays. They are also used for the database analysis report. |
Fragmentation Analysis Mode |
Detailed is the default. The other options are Sampled and Limited. This option serves as a parameter that’s passed to the space diagnostic procedures use on the Objects tab for the reorganization summary and the detail views for indexes and tables. |
Completing the Space Analyst Thresholds Options Dialog Box for Space Analyst
To complete this dialog box, do the following:
- On the Analysts Toolbar, click the Options button.
- The Space Analyst opens the dialog box.
- Review thresholds.
- Click OK.