Session 5: Correcting Space-related Problems Using Space Analyst for Microsoft

From DBArtisan
Jump to: navigation, search

Go Up to Space Analyst for Microsoft SQL Server Tutorial

After you have identified space-related bottlenecks in your database with Space Analyst, you can use a number of different tools and facilities within the product to eliminate each storage problems.

Correcting Storage Structure ‘out of space’ Problems Using Space Analyst for Microsoft

Space Analyst can help you quickly remedy databases and transaction logs that are running out of room by:

  • Resizing an existing database or transaction log to have more room.
  • Adding a new filegroup or file to an existing database or transaction log.
  • Setting the Autogrowth attribute for one or more of a database's or transaction log's files to allow for automatic growth.

To manage these functions, do the following:

  1. Select the Databases tab.
  2. In the Database Summary section, right-click the graph, and then select Toggle Display.
  3. Select the database that is running low on free space.
  4. Select the Open toolbar button or the Open shortcut menu option.
    DBArtisan opens the Database Manager.

In the Database Manager, you can remedy the database problem in one of three ways:

  • To resize an existing database or transaction log, open one of the database's or log's files in the editor (using the Edit button) and change the file's size to a new, larger size. Then close the editor and select the Alter toolbar button to make the change permanent to the database.
  • To add a new file or file group to the database or transaction log (note: you can't add new filegroups to a transaction log, only files), select the Add button in the editor, and specify the new filegroup or file's attributes. Once complete, close the Add Datafile dialog and select the Alter toolbar button to make the change permanent to the database.
  • To set Autogrowth for one or more of a database's or transaction log's files, open each file in the file editor by selecting the file and then pressing the Edit button. Once in the Modify File dialog, select the option for Allow Growth and specify other attributes as needed. When complete, close the dialog and select the Alter toolbar button to make the change permanent to the database.

Removing Wasted Space in Databases or Files Using Space Analyst for Microsoft

Space Analyst can help you quickly eliminate unused space in a database or database file. The unused space can be released back to the operating system for other use. To remove unused space from a database, do the following:

  1. To shrink an entire database, right-click a database in any of Space Analyst's grids, and then select Shrink Database.
    DBArtisan opens the Shrink File dialog.
  2. To shrink a specific file in a database, select the Filegroups/Files tab and then view files by selecting the Files radio button.
  3. Right-click a file and select Shrink File.
    DBArtisan opens the Shrink File dialog.
  4. Select desired options.
  5. Click Execute to make the change permanent.

Correcting Space-related Object Problems for Space Analyst for Microsoft

Once you identify specific objects that need storage corrections, you can use Space Analyst to help you quickly resolve your found issues. The Space Management wizard inside of Space Analyst can be used to build reorganization and space maintenance jobs that run right the first time. All jobs can either be immediately run or scheduled for later execution.

To create a standard database or object reorganization job, do the following:

  1. Click the Open Wizard button.
  2. Select New Space Management Task.
  3. Click Next.
  4. Select Create standard reorganization job.
  5. Click Next.
  6. Select the databases and objects you would like to reorganize from the Explorer tree on the left hand side of the wizard. You can reorganize full databases, selected schema accounts, or selected objects.
  7. Click Next.
  8. The next panel allows you to customize the reorganization. You can either set options at the global level (affects all objects) or specific object level. Using the options, you can relocate objects to different filegroups, perform online index rebuilds, and specify other customizations.
  9. Click Next.
  10. The next panel builds your reorganization script and allows you to preview the operation. Click Next when ready.
  11. Click Next.

The final panel allows you to open the reorganization script in a SQL editor, immediately run it, or schedule it for later execution.

Proactive Space Management Using Space Analyst for Microsoft

If you have many databases to take care of, then you might want to relieve the burden of performing detailed storage analysis on every database, each of which may or may not require attention. Fortunately, Space Analyst makes it easy to create smart space management jobs that can automate this task for you.

You can create two specialized space management jobs that allow you to proactively eliminate space threats before they affect your database:

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.

These "smart" space maintenance jobs allow you to truly "set and forget" the task of monitoring and reorganizing the space of your critical databases.