Session 3: Identifying Storage Structure Problems in Space Analyst for Microsoft

Go Up to Space Analyst for Microsoft SQL Server Tutorial

Space Analyst can help you quickly find storage structure problems that threaten the availability of your database.

Pinpointing ‘out of space’ Problems Using Space Analyst for Microsoft

To quickly tell if any of the tablespaces in your database are about to run out of free space, do the following:

1. Select the Storage Overview tab.
2. In the Database Overview and Log Overview sections, position the pointer over the graph.
3. Right-click and select Toggle Display.

Examine the Percent DB Used columns of each database and transaction log along with the DB Can Grow column. If a database or transaction log is nearing the end of free space and cannot automatically grow, then action should be taken to prevent an out-of-space error.

You can also obtain graphical views of databases and pertinent storage property information by viewing the Databases tab of Space Analyst. You can visually get a handle on the amounts of table, index, and free space of your databases and quickly view critical storage property facts such as the number of databases that cannot automatically grow in size if needed.

All the graphs can be toggled to grids by using the right mouse menu option of Toggle Display. When in grid mode, very detailed information is available concerning database and transaction log space. Note that the graph is scrollable so you may have to scroll to see all your databases.

You can perform a number of actions on a database, such as shrinking it or invoking database-related DBCC operations by using either the right mouse menu or the Command toolbar button (the yellow arrow).

Viewing Filegroup and File Details Using Space Analyst for Microsoft

You can also view space usage at the filegroup and file levels. To get summary and detailed information for filegroups and files, follow these steps:

1. Invoke Space Analyst and view the File Groups/Files tab.

   The default presentation is a graphical overview of filegroups. Note that the graph is scrollable so you may have to scroll to see all your filegroups.

2. To view detailed information for filegroups, right-click and select Toggle Display to view the grid.
3. To view file information instead of filegroup metrics, select Files.

When viewing filegroup and file information, you can perform a number of actions on selected filegroups and files such as extracting their DDL or shrinking the parent database or selected file. All options are available by using either the shortcut menu or Command menu.

Using Analysis Reports in Space Analyst for Microsoft

You can run HTML-formatted reports that pinpoint all major storage-related problems for a particular tablespace and the objects that are contained within it. To produce an Analysis Report, do the following:

1. On the toolbar, click Analysis Report.

   Space Analyst opens the Analysis Report dialog.

2. In Report File Name and Report Title you can change the location and report title of the report.
3. In Category, select the types of space problems you would like to investigate. The default option is All.
4. In Database, select the database you would like to analyze.
5. In File Group, select the filegroup level.
6. Click Execute.

   Space Analyst creates a frame-based Analysis report for the database.

Proceed to Session 4: Pinpointing Object Performance Inefficiencies Using Space Analyst for Microsoft