Creating a Free-Space Threshold for the Log Segment

When the last-chance threshold is crossed, all transactions are aborted or suspended until sufficient log space is freed.

In a production environment, this can have a heavy impact on users. Adding a correctly placed free-space threshold on your log segment can minimize the chances of crossing the last-chance threshold.

The additional threshold should dump the transaction log often enough that the last-chance threshold is rarely crossed. It should not dump it so often that restoring the database requires the loading of too many tapes.

This section helps you determine the best place for a second log threshold. It starts by adding a threshold with a free_space value set at 45 percent of log size, and adjusts this threshold based on space usage at your site.

Use this procedure to add a log threshold with a free_space value set at 45 percent of log size.

Determine the log size in pages:

select sum(size)
from master..sysusages
where dbid = db_id("database_name")
and (segmap & 4) = 4

Use sp_addthreshold to add a new threshold with a free_space value set at 45 percent. For example, if the log’s capacity is 2048 pages, add a threshold with a free_space value of 922 pages:
```
sp_addthreshold mydb, logsegment, 922, thresh_proc
```
Create a simple threshold procedure that dumps the transaction log to the appropriate devices.