Managing Free Space with Thresholds

When you create or alter a database, you allocate a finite amount of space for its data and log segments. As you create objects and insert data, the amount of free space in the database decreases.

This chapter explains how to use thresholds to monitor the amount of free space in a database segment.

Monitoring free space with the last-chance threshold

Crossing the threshold

Controlling how often sp_thresholdaction executes

Rollback records and the last-chance threshold

Calculating the space for rollback records

Determining the current space for rollback records

Effect of rollback records on the last-chance threshold

User-defined thresholds

Last-chance threshold and user log caches for shared log and data segments

Reaching last-chance threshold suspends transactions

Using alter database when the master database reaches the last-chance threshold

Automatically aborting or suspending processes

Using abort tran on log full to abort transactions

Waking suspended processes

Adding, changing, and deleting thresholds

Displaying information about existing thresholds

Thresholds and system tables

Adding a free-space threshold

Changing a free-space threshold

Specifying a new last-chance threshold procedure

Dropping a threshold

Creating a free-space threshold for the log segment

Adding a log threshold at 45 percent of log size

Testing and adjusting the new threshold

Creating additional thresholds on other segments

Determining threshold placement

Creating threshold procedures

Declaring procedure parameters

Generating error log messages

Dumping the transaction log

A simple threshold procedure

A more complex procedure

Deciding where to put a threshold procedure

Disabling free-space accounting for data segments

Restoring system tables with disk
reinit and disk refit


	Expand Search